offset() function #VALUE!

[H.C. Chen]

1. Name="Data" B4:B11 = {"data", 1,2,3,4,5,6,7}
2. formula =OFFSET(Data,1,0,3,1) 's value is 2 when at E6 , seems fine
3. But same formula becomes an error #VALUE! when at E5

Why different result happens at E5 and E6 with same formula? I think
=OFFSET(Data,1,0,3,1) has nothing to do with its own position.

 

[Billy Liddel]

Offset uses a single cell as the reference. When You use a range name for the
reference then Excel uses the first cell as the reference. You have set the
Height of three so when the formula is copied it will come to empty cells and
produce an error.

Using the same data 1 to 8 in cells B4:B11 the following formula produces
the sum of the next three rows. Note: The reference row is not absolute.

=SUM(OFFSET($B4,0,0,3))

Not value is returned and the totals wil decrease when there is no value in
the cell covered by the Height. Extend up and down and the value will
eventually be zero.

HTH
Peter Atherton

 

[H.C. Chen]

You have set the Height of three so when the formula is copied it will come
to

empty cells and produce an error.

But there's no empty cells in the Height of three! I make it clearer by this
example :
B4:B11 = {"data";11;22;33;44;55;66;77}
J4 =OFFSET($B$4,1,0,3,1) = #VALUE! <-------- 11 is expected but why error?
J5 =OFFSET($B$4,1,0,3,1) = 11 <--------------- Correct
J6 =OFFSET($B$4,1,0,3,1) = 22 <----- I think it should be still 11, but why
22?

 

[T. Valko]

It's because you have the height argument set to 3 and the function returns
a 3x1 array. In cell J4, if you array enter the formula it will return the
correct result. Because the formula returns an array and the other cells,
J5:J6, are within the *implicit intersection* of the formulas range you get
*correct* results in J5:J6.

Try entering the formula in cell A10 then copy down a few cells. You'll get
all results of #VALUE!.

The implicit intersection is: the nth element of an array that refers to a
cell in the same row/column that a normally entered array formula is entered
in. Does that make sense? That might have to be read a few times to
understand!!!

See if this helps:

...........B..........J
4.....data........=OFFSET($B$4,1,0,3,1)
5......11.........=OFFSET($B$4,1,0,3,1)
6......22.........=OFFSET($B$4,1,0,3,1)

If you normally enter the formula in J4 and copy down to J6 the *correct*
results are:

#VALUE!
11
22

As written the formula returns a 3x1 array comprised of the range B5:B7.
Since the formula wasn't array entered the formula in J4 returns the #VALUE!
error. Now, here's where the implicit intersection rule comes into play. The
formula in J5 is on the same row as B5 which is the first element in the
height argument array returned by the formula. The formula in J6 is on the
same row as B6 which is the second element in the height argument array
returned by the formula. The formula in J4 refers to B5 but since the
formula was not array entered and is not on the same row as B5 (not within
the implicit intersection) the result is an error.

For your formula to work properly you need to select a 3x1 array of cells
then array enter the formula as "block array".


expbiff101

 

[Billy Liddel]

HC

You need to reset the offset value and use Hieght of one. When you are
copying down the ROWS function is useful when a change of offset value is
needed.

J4: =OFFSET($B$4,ROWS($1:1),0,1,1)
J5: =OFFSET($B$4,ROWS($1:2),0,1,1)
J6: =OFFSET($B$4,ROWS($1:3),0,1,1)
=OFFSET($B$4,ROWS($1:4),0,1,1)
=OFFSET($B$4,ROWS($1:5),0,1,1)
=OFFSET($B$4,ROWS($1:6),0,1,1)
=OFFSET($B$4,ROWS($1:7),0,1,1)

Enter the first fomula in J4 then copy down as far as required. See how the
ROWS increments by one as it is copied down, it is the second value that is
used.

Hope this helps.
Peter Atherton

 

[Billy Liddel]

Looking at your post again it seems that you want to stay on offset 1 so the
formula should be

J4: =OFFSET($B$4,1,0,1,1)

and copied down. However, if you want to change the lookup value in the
future you could have the value in a cell, say E3 and use the formula

J4: =OFFSET($B$4,E3,0,1,1)

Then you can enter a new number in E3 to change the offset value.

Peter