# Value# error with OFFSET

#### paul_taylor

##### New Member
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?

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

#### DonkeyOte

##### MrExcel MVP
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

##### New Member
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

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

Code:
``=SUM(OFFSET(B5,0,0,20,1))``

#### paul_taylor

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

Code:
``=SUM(OFFSET(B5,0,0,20,1))``

OK, thanks.

#### paul_taylor

##### New Member
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

##### New Member
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.

Replies
1
Views
59
Replies
32
Views
567
Replies
4
Views
133
Replies
2
Views
44
Replies
1
Views
93

1,191,214
Messages
5,985,312
Members
439,957
Latest member
venky2002

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back