天道酬勤,学无止境

Excel formula reference cell above last cell in range

Question

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?

Answer1

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.

Answer2

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.

Restricted HTML

  • Allowed HTML tags: <a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd> <h2 id> <h3 id> <h4 id> <h5 id> <h6 id>
  • Lines and paragraphs break automatically.
  • Web page addresses and email addresses turn into links automatically.

相关推荐