Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Why does following Excel function return an error? The error occurs only when the reference that is returned is larger than 1 cell. In the formula, the returned reference is 1 row high and 2 rows high.

    =OFFSET($A$2,ROW(G6),0,1,2) 
share|improve this question
    
Are you sure about the ROW-function? Cause I tried to reproduce your case and it seems to me that the ROW-Function is irrelevant. Can you please try it with a 6 instead of ROW(G6). You should get the same #VALUE error without the ROW-function –  nixda Dec 29 '12 at 6:47
    
It's an array formula, so it needs to be entered using the Control-Shift-Enter key combination. Doing that, the formula using 6 rather than the row(g6) will pick up the value in cell A8. If the offset formula is extended to the right by one cell, it will also pick up the value in cell B8. (To extend the formula to the right, you have to select the formula and the cell to the right, press the F2 key, and the Control-Shift-Enter). On the other hand, using the function ROW(g6), which should produce a value of 6, the offset formula returns the #VALUE! error. –  chuff Dec 29 '12 at 8:16

1 Answer 1

up vote 1 down vote accepted

There are two different ways that your formula might return #VALUE! error here, depending on how you are trying to employ it:

Firstly if you have a formula which returns an array of values then it makes a difference where you put the formula (if not "array entered").

Put that formula in A10 (not array entered) and you see the value from A8, put the formula in B10 and you see the value from B8, put the formula somewhere other than columns A and B and you get #VALUE! That's standard behaviour in Excel for these type of functions.

Secondly, if you try to "array enter" the formula in a 2 cell horizontal range to see both values (as chuff suggests), then you also get #VALUE! error which is caused by ROW function.

That happens because ROW() returns an "array" (even when it's a single row like here) which some functions don't process properly - you can see that if you highlight ROW(G6) and press F9 key - you see {6} rather than 6. The solution is to either use just 6 in the formula or, presuming you want the flexibility provided by ROW, you can wrap in another function, like SUM, e.g.

=OFFSET($A$2,SUM(ROW(G6)),0,1,2)

several other functions will do the same job as SUM here, e.g. MAX, MIN.....or you can replace ROW with ROWS, e.g.

=OFFSET($A$2,ROWS(G$1:G6),0,1,2)

share|improve this answer
    
Thank you, @barry, I hadn't thought of using ROWS in the way you suggest, which will get the job done in this particular instance. –  chuff Dec 29 '12 at 13:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.