=OFFSET cell function help needed
Posted by JAdolphs on April 06, 2001 1:25 PM
I have been trying to use the =OFFSET function, but I am running into problems. When I follow the exact examples exactly as listed in the Excel help screen, I can get the first example to work, but I can't get the second example to work. I have filled cells C2:F5 with a simple grid of numbers to test these examples. Running the first example returns the value of cell F5 as described. Running the second example returns #VALUE!, not C2:E4 as shown. Any advice?
Posted by Scott R on April 06, 2001 1:42 PM
It's working correctly. The 2nd example returns a reference to cells C2:E4 that by itself doesn't return a meaningful value. You would need to use that reference within another formula, e.g. SUM(OFFSET(C3:E5,-1,0,3,3)).
You can verify that Example 2 is returning C2:E4 by entering GoTo {F5} OFFSET(C3:E5,-1,0,3,3).
Posted by Ed Ziarko on April 09, 2001 10:48 PM
Regarding the "OFFSET" function explanation in HELP. The second example, i.e. "OFFSET (C3:E5, -1,0,3,3) equals C2:E4" is misleading. That example, when entered in a cell (C7 for instance) will return a "#VALUE" error! Apparently, you will always get a "#VALUE" error when you specify a range in the reference part of the OFFSET formula unless you are using it as shown in the Remarks example, as an argument to another function. The entire explanation of OFFSET is confusing in that 'returns a reference' implies that the reference will be displayed but what is displayed is actually the value in the cell being referenced.
