return last value>0

  • Thread starter Thread starter shawn modersohn
  • Start date Start date
S

shawn modersohn

I want to have a cell at the bottom of list named "current value" that
returns the last value in a column that is greater than 0. What function
would I use? Thank you.
 
Hi Shawn
one way: try the following array formula (entered with
CTRL+SHIFT+ENTER):
=INDEX(A1:A999,MAX(IF(A1:A999>0,ROW(A1:A999),0)))

note: this won't work if there are also text entries in your range!
Frank
 
Frank Kabel said:
Hi Shawn
one way: try the following array formula (entered with
CTRL+SHIFT+ENTER):
=INDEX(A1:A999,MAX(IF(A1:A999>0,ROW(A1:A999),0)))

note: this won't work if there are also text entries in your range!
Frank
Click to expand...

As I have it now current value =INDEX(B2:B3,MAX(IF(B2:B3>0,ROW(B2:B3),0)))

Date Item
7
0
current value
0
The array acts opposite as intended, current value is shown as, 0 and if any
number other than 0 is in B2, an error result.
 
Hi Shawn
as you are starting in row 2, adapt the formula:
=INDEX(B2:B999,MAX(IF(B2:B999>0,ROW(B2:B999),0))-1)
Frank
 
Of course, thank you very much.

Frank Kabel said:
Hi Shawn
as you are starting in row 2, adapt the formula:
=INDEX(B2:B999,MAX(IF(B2:B999>0,ROW(B2:B999),0))-1)
Frank
Click to expand...
 
Back
Top