Search code examples
excelexcel-formulaexcel-2013

Excel formula reference cell above last cell in range

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?

Solution

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