CODEWITHSUNDEEP
excelexcel-formulaexcel-2010vba
Carol.Kar
Carol.Kar

Reputation: 5355

Offset function returns #VALUE! with small dataset

I have some data in excel and want to access certain rows with the offset function, however the function returns #VALUE! for my small dataset.

Using this, OFFSET(A2;0;0;COUNTA(A2:A99999);1) I get #VALUE!. When changing the formula to OFFSET(A2;0;0;100;1) I get a value.

enter image description here

Any suggestions, what I am doing wrong?

I appreciate your replies!

Upvotes: 2

Views: 699

Answers (2)

user4039065
user4039065

Reputation:

If =OFFSET(A2;0;0;COUNTA(A2:A99999);1) is your full formula you are getting a result with a hard-coded number due to Inferred Position (I hope that's the right term). This means that when you are referencing a range but are not processing the full range the operative cell is the one relative to the current position.

If you put =$A$2:$A$11 in D2 and fill down you will get processive results according to your row position that match column A. Keep filling down and you will get the #VALUE! error when you go past row 11.

This is why =OFFSET(A2; 0; 0; 100; 1) returns a zero and =OFFSET(A2; 0; 0; COUNTA(A2:A99999); 1) returns a #VALUE! error. The first formula is the same as saying =$A$2:$A$101 and it is returning zero because you put the formula in a cell that was above row 101 (referencing a blank cell in column A that returned a value of 0). The second (with the COUNTA function) is like saying =$A$2:$A$11 and you are putting it into a cell in row 13 (below row 11).

Addndum:

On a related note, these variations of the INDEX function and MATCH function are non-volatile and more functional than the OFFSET function while providing a cell range reference identical to what you were achieving with OFFSET and the COUNTA function.

'for text in a column
=$A$2:INDEX($A:$A, MATCH("zzz", $A:$A))

'for numbers in a column
=$A$2:INDEX($A:$A, MATCH(1e99, $A:$A))

'for unknown in a column
=$A$2:INDEX($A:$A, IFERROR(MATCH(1e99, $A:$A), MATCH("zzz", $A:$A)))

'for combined in a column
=$A$2:INDEX($A:$A, MAX(MATCH(1e99, $A:$A), MATCH("zzz", $A:$A)))

'I don't like COUNTA but it works as long as there are no blanks
=$A$2:INDEX($A:$A, COUNTA($A:$A))

There are many variations on this method. Perhaps you can use one of the above for your purposes.

Upvotes: 2

tea_pea
tea_pea

Reputation: 1542

OFFSET(A2;0;0;(range > 1);1) will return an array rather than the value of a single cell. This only makes sense if you put sum or some other aggregate function around it. (What are you trying to achieve with the formula?)

e.g.

=SUM(OFFSET(A2;0;0;COUNTA(A2:A99999);1))

Upvotes: 2

Related Questions