Value# error with OFFSET

[paul_taylor]

This is killing me.

Dataset that starts at b5 and goes down.

If I put in a formula in cell f1 that goes like this

=OFFSET(B5,0,0,20,1)
I get #Value! error

If I do this
=OFFSET(B1,4,0,20,1)
I get same error

If I do this
=OFFSET(B1,0,0,20,1)
It works. I don't get an error.


What am I not understanding about OFFSET?

 

[DonkeyOte]

The OFFSET as you have it will return a 20 value Array.

In this form only the value associated with the current row will be returned.

In the first two formulae the "current row" is outside of the rows used in the OFFSET (row 1 vs rows 5:24) - hence the error.
Were you to copy the same formula to F5 you would get the content of B5 returned.

In the last formula the "current row" (1) resides within the OFFSET range hence content of B1 is returned (must be blank or 0)

 

[paul_taylor]

The OFFSET as you have it will return a 20 value Array.

In this form only the value associated with the current row will be returned.

In the first two formulae the "current row" is outside of the rows used in the OFFSET (row 1 vs rows 5:24) - hence the error.
Were you to copy the same formula to F5 you would get the content of B5 returned.

In the last formula the "current row" (1) resides within the OFFSET range hence content of B1 is returned (must be blank or 0)

So are you saying, even though the current row may show Value error, the array still works?

 

[DonkeyOte]

Yes - if you apply to it an appropriate outer call of some sort, eg:

=SUM(OFFSET(B5,0,0,20,1))

 

[paul_taylor]

Yes - if you apply to it an appropriate outer call of some sort, eg:

=SUM(OFFSET(B5,0,0,20,1))

OK, thanks.

 

[paul_taylor]

OK, thanks.

OK, so I'm still not getting it right. When I name the cell where the OFFSET exists and then chart it, I get the following.

first of all the offset formula = =OFFSET(B4,1,0,10,1)

I would like to chart the 10 data points starting in b4 and going down.

When the cell for the offset is in b1, the cell value is #value! and the chart is blank.

When I cut and paste the cell to d5, the cell value returns 18, which is the value in b5, and the chart shows just one data point of 18.

When I cut and paste the cell to d10, the cell value returns 3, which is the value in b10, and the chart shows just one data point of 3.

 

[paul_taylor]

OK, so I'm still not getting it right. When I name the cell where the OFFSET exists and then chart it, I get the following.

first of all the offset formula = =OFFSET(B4,1,0,10,1)

I would like to chart the 10 data points starting in b4 and going down.

When the cell for the offset is in b1, the cell value is #value! and the chart is blank.

When I cut and paste the cell to d5, the cell value returns 18, which is the value in b5, and the chart shows just one data point of 18.

When I cut and paste the cell to d10, the cell value returns 3, which is the value in b10, and the chart shows just one data point of 3.

Apparently I can't use a cell to put the offset in and name the cell as the range, I need to put the offset formula directly into the Name Manager? That worked.