Last value in a range

[stvgarner]

How can I get the last value in a range when it could end at any point and there could be blanks in the range anywhere?

Example:

A1 22.23
A2 (is blank)
A3 13.45
A4 34.55
A5 (is blank)


I want the formula to return 34.55, but tomorrow there may be something in cell A5 and I would then want it to return the value in A5.


Thanks!

[BigBas]

Try this Array formula and let me know if it works for you:

=INDEX(A:A,LARGE(IF(NOT(ISBLANK(A1:A100)),ROW(A1:A100)),1))

NOTE: This is an array formula. Confirm it using CTRL+SHIFT+ENTER, not just enter.

[stvgarner]

That seems to do it. Thanks! (thanks for the ctl-shift-enter part too, I would have been going crazy trying to figure out what I was doing wrong)

[BigBas]

Glad I could help.