Need value of last cell that <> na() in my range?

  • Thread starter Thread starter Keith R
  • Start date Start date
K

Keith R

Trying to accomplish this with an formula; I have a range of cells (all in
the same column) and I need to get the value of the last cell that _isn't_
equal to na(). The data shows year-to-date information, and I need to get
the last month that actually has data in it, and compare that number to a
target value (elswhere on the sheet). cells without data have na() because
of the formulas that are in those cells.

I don't usually do much with array formulas (except maybe simple ones) and
I'm kinda stuck here-
I thought this would at least give me which row to pull the value from, but
it doesn't like the syntax
=MAX(IF(ISNA(C20:30),"",ROW(C20:30)))

although this works fine (not an array):
=MAX(IF(ISNA(C20),"",ROW(C20)))

I believe that ISNA is taking the whole range at once instead of a cell at
a time (like I want it to with the array formula)
in which case, it would return "" and the max doesn't have anything to work
with, which would be why (I think) it
is erroring out.

Any and all help appreciated-
Thanks,
Keith R
XL97

p.s.
Once I get the row, I could always do something like = indirect ("A" &
MAX(IF(ISNA(C20:30),"",ROW(C20:30))))
then compare it with something like this, where B1 holds the target value:

=if (indirect ("A" & MAX(IF(ISNA(C20:30),"",ROW(C20:30)))) > B1, "met
target","did not meet target")
 
Keith

Try the following array formula:

=INDEX(B10:B30,MAX(IF(ISNA(B10:B30),FALSE,ROW(B10:B30)))-ROW(B10)+
1)

Since this is an array formula, you must press Ctrl+Shift+Enter
rather than just Enter when you first enter the formula and
whenever you edit it later. If you do this properly, Excel will
display the formula enclosed in curly braces {}.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)
 
Back
Top