Last 2 values in range

J

JL

I need help with a worksheet formula. In rows 2 through 25
I have formulas that bring in values from other sheets.
Some of the cells have #DIV/0! in them because it won't
calculate until later in the year when the numbers come
in. In cell 30 I want to display the difference between
the last 2 cells in the column that have values displayed,
but don't know how. I was hoping to find a function
similar to the VBA Range("B65536").End(xlUp).Row command.
Any ideas?
 
D

Domenic

For the last numerical value...

=LOOKUP(9.99999999999999E+307,A2:A25)

For the second last numerical value...

=INDEX(A2:A25,LARGE(IF(ISNUMBER(A2:A25),ROW(A2:A25)-ROW(A2)+1),2))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!
 
J

JL

Works perfect, thank you so much.
-----Original Message-----
For the last numerical value...

=LOOKUP(9.99999999999999E+307,A2:A25)

For the second last numerical value...

=INDEX(A2:A25,LARGE(IF(ISNUMBER(A2:A25),ROW(A2:A25)-ROW (A2)+1),2))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!


.
Click to expand...
 
Top