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" wrote:

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.

--
事情不耐做，問題不耐看。