get lowest row value in column

[rainman101]

Hello,

I'd like to know a formula for returning the lowest non-blank value in a column. Imagine a checkbook register, where the balance column will naturally fill down with time. I'd like to see the ending balance, no matter what row it is on, in a cell on a different sheet. I there a way to do this?

[Andrew-R]

If your balance is in column A on sheet1 then try:

=LOOKUP(REPT("z",255),Sheet1!A:A)

Edited to add: Stupid me, that doesn't work with numeric values.

Instead try:

=INDEX(Sheet1!A:A,MAX(IF(Sheet1A1:A1000<>"",ROW(Sheet1!A1:A1000))),0)

Entered as an array formula, i.e. you must enter it using Ctrl-Shift-Enter, not just enter.

[rainman101]

Thanks! I'll try this when I get home and let you know how it worked.

[James Gerlach]

Here's another way:

=INDEX(E4:E12,MATCH(0,E4:E12,-1),0) where E4:E12 is the location of the column of numbers

[Haseeb Avarakkan]

This will return the LAST NUMERIC value in column A:A

=LOOKUP(9E300,A:A)

This will return the LAST TEXT value in column A:A

=LOOKUP(REPT("z",255),A:A)

This will return the LAST VALUE (regardless of entry, Number or Text) in column A:A

=INDEX(A:A,MAX(IFERROR(MATCH(REPT("z",255),A:A),1),IFERROR(MATCH(9E300,A:A),1)))

Or a short one with CTRL+SHIFT+ENTER

=INDEX(A:A,MAX(IFERROR(MATCH({"zzzzzzzzzzzzzzz",9E300},A:A),1)))

[NBVC]

OR

=LOOKUP(9.999999E+307,Sheet1!A:A)

[rainman101]

Thanks guys. You're awesome.