I already saw a few related questions on here about finding the last item but my question is a bit different because I'm trying to find the last not blank or non zero value in a column and either return that value or return 0 if there is nothing there. I have written a formula that works for me except for the very first row in the column and returns an error because it can't find a value. I tried to change the formula to return a 0 if it doesn't find anything but apparently wound up making the problem worse. Here is my current attempt at a formula:
=IF(B12=0,0,IF(LOOKUP(2,1/($B$10:B11<>0),$B$10:B11)=0, 0, B12 - IF(ISBLANK(LOOKUP(2,1/($B$10:B11<>0),$B$10:B11)), 0, LOOKUP(2,1/($B$10:B11<>0),$B$10:B11))))
1 Answer
I would like to suggest few formula works in different situations:
Formula returns last non-blank, non-zero value in column or range:
=LOOKUP(2,1/(A:A<>0),A:A)=LOOKUP(2,1/(A2:A10<>0),A2:A10)Another is formula returns last non blank non zero positive value only in column or range:
=LOOKUP(1,0/(0+A:A>0),A:A)
N.B.
Second formula is an optional.
Adjust cell references in the formula as needed.
