CODEWITHSUNDEEP
excelexcel-formulaexcel-2010formulaoffset
Shaun Greatrix
Shaun Greatrix

Reputation: 85

Excel Offset is returning a #VALUE! error when the height is >1 (Excel 2010)

I've set up Names with the intention of using them to return data ranges for a line chart. The X values are "GI", "IE" and "EE". The Y value is "DATE".

However, my "DATE" and "GI" names are returning "#VALUE!" errors - whereas IE and EE are not.

So far, I have found that this error occurs when the height value (CountIf below) is more than 1.

The cell range, and beyond to 2000-and-something, are dynamically generated from user selections to form a Date Range. Ergo the use of CountIf rather than CountA.

Any help would be much appreciated. This is the last leg of a difficult workbook!

DATE:

=OFFSET(Graph!$B$8,0,0,COUNTIF(Graph!$B$8:$B$2927,">"&0)-1)

GI:

=OFFSET(Graph!$C$8,0,0,COUNTIF(Graph!$C$8:$C$2927,">"&0)-1)

IE:

=OFFSET(Graph!$D$8,0,0,COUNTIF(Graph!$D$8:$D$2927,">"&0)-1)

EE:

=OFFSET(Graph!$E$8,0,0,COUNTIF(Graph!$E$8:$E$2927,">"&0)-1)

Information:

      B             C       D   E
7     DATE          GI      IE  EE
8     25/04/2011    0       0   0
9     26/04/2011    0       0   0
10    27/04/2011    0       0   0
11    28/04/2011    0       0   0
12    29/04/2011    0       0   0
13    30/04/2011    0       0   0
14    01/05/2011    0       0   0
15    02/05/2011    0       0   0
16    03/05/2011    0       0   0
17    04/05/2011    0       0   0
18    05/05/2011    0       0   0
19    06/05/2011    0       0   0
20    07/05/2011    0       0   0
21    08/05/2011    0       0   0
22    09/05/2011    0       0   0
23    10/05/2011    18000   0   0
24    11/05/2011    18000   0   0
25    12/05/2011    18000   0   0
26    13/05/2011    18000   0   0
27    14/05/2011    18000   0   0
28    15/05/2011    18000   0   0
29    16/05/2011    18000   0   0
30    17/05/2011    18000   0   0
31    18/05/2011    18000   0   0
32    19/05/2011    18000   0   0
33    20/05/2011    18000   0   0
34    21/05/2011    18000   0   0
35    22/05/2011    18000   0   0

Notably, the chart isn't reading it entirely right, either.

Upvotes: 0

Views: 1639

Answers (2)

Forward Ed
Forward Ed

Reputation: 9874

So based on your data, an going a slightly different route than offset (offset route should work) I used the index route.

for the x axis I used

=INDEX($B$9:$B$36,MATCH($C$5,$B$9:$B$36,0)):INDEX($B$9:$B$36,MATCH($D$5,$B$9:$B$36,0))

I used a defined name of X_axis

X_axis

for the y axis I used

=INDEX($C$9:$C$36,MATCH($C$5,$B$9:$B$36,0)):INDEX($C$9:$C$36,MATCH($D$5,$B$9:$B$36,0))

I used a define name of Y_axis. For your second series on the Y axis, you would need to change the reference range from C9:C36, to the appropriate column that is lined up with your dates.

When defining the series, I had to use the workbook name in conjunction with the named range. so series data looked like this:

enter image description here

Proof of Concept

Proof of Concept

Upvotes: 1

Sun
Sun

Reputation: 762

This formula should create the correct named Range for date:

=OFFSET(Sheet1!$B$8,0,0,MATCH(Sheet1!$D$4,Sheet1!$B$8:$B$2927,0),1)

For GI: 

=OFFSET(Sheet1!$B$8,0,1,MATCH(Sheet1!$D$4,Sheet1!$B$8:$B$2927,0),1)

For IE:

=OFFSET(Sheet1!$B$8,0,2,MATCH(Sheet1!$D$4,Sheet1!$B$8:$B$2927,0),1)

For EE:

=OFFSET(Sheet1!$B$8,0,3,MATCH(Sheet1!$D$4,Sheet1!$B$8:$B$2927,0),1)

(D4 contains the end date dropdown.)

In the data selection for the graph, it is important to write the named Range including the sheet it's on, e.g.: =Sheet1!nrDate instead of just =nrDate.

Please let me know if this works for you.

Upvotes: 2

Related Questions