Excel formula reference cell above last cell in range

0
votes

I'm trying to set up a formula to automatically calculate the % change between the most recently added cell in a range (which includes #N/A values at the bottom of the range) and the cell immediately above it. I've been using this formula to obtain the value of the bottom not #N/A cell:

LOOKUP(2, 1/NOT(ISNA(G8:G19)), G8:G19)

Which is working fine. My first thought on how to reach the cell above it was to use OFFSET, like so:

OFFSET(LOOKUP(2, 1/NOT(ISNA(G8:G19)), G8:G19), -1, 0)

but this gives me an error, I think because the lookup function is returning the value in the cell rather than the cell reference. How should I format a function to return the value of the cell above the last non-N/A cell in a range?

2
excelexcel-formulaexcel-2013
What if the one above the last non-NA cell is a NA cell? – user4039065

2 Answers

1
votes

Try this alternative for seeking the last non-error, numerical value in column G.

=index(G:G, match(1e99, G:G))/index(G:G, match(1e99, G:G)-1)

Using MATCH to find the last number in a column returns the row number to INDEX. It is a simple matter to subtract 1 from a row number.

1
votes

One method is to use this array formula:

=INDEX($G$8:$G$19,MATCH(2,IF(NOT(ISNA($G$8:$G$19)),1))-1)

Being an array formula it must be confirmed with Ctrl-Shift-Enter on exiting edit mode instead of enter. If done properly then Excel will put {} around the formula.

enter image description here