Archive of Mr Excel Message Board

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).

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.