"Search Column for Last Numeric Value"

[Jim Badinger]

Greetings,
I would like for a worksheet to search a
particular column for
the last numeric value...that is to say the last value
(cell) that is an
an actual number and not the "IF" scenario I created which
if the conditions
do not meet any of the criteria set then the cell is left
with a "N/A"
to show nothing occured. I would like it to search the
column down until
it finds the last number in it and then perform some
calculation.

The calculation (math) I don't have any problems with its
just this "search"
function, I've tried a couple different things but I can't
seem to hit it.

Exammple:

A B C D
1 Q R 12 S

2 Q R 13.5 S

3 Q R 10 S

4 Q R N/A S

Q=SOME TEXT VALUE
R=SOME TEXT VALUE
S=SOME TEXT VALUE
C COLUMN IS THE NUMERIC COLUMN WITH THE "N/A" value that
ends the search
for the last cell (C3) that has the last numeric value.
The cell in C column
will change for each worksheet.

Thanks,
J. Badinger

 

[Tom Ogilvy]

=INDEX(C:C,MAX(IF(ISNUMBER(C1:C50),ROW(C1:C50)),1))



Entered with Ctrl+Shift+Enter rather than just enter (since this is an array
formula) will get the last numeric value.

Adjust the 50 to the last row that needs to be checked - but be conservative
since the more cells checked, the slower the recalculate.

 

[Tom Ogilvy]

Faster would be

=Index(C:C,Match(9.99999999999999E+307,C:C,1),1)

Regards,
Tom Ogilvy

 

[Jim Badinger]

Very helpful, I have found other uses for this now!!!

Thanks again