Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
return last value>0
85 views
Skip to first unread message
shawn modersohn
Feb 11, 2004, 4:18:20 PM2/11/04
to
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.
returns the last value in a column that is greater than 0. What function
would I use? Thank you.
Frank Kabel
Feb 11, 2004, 4:24:13 PM2/11/04
to
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)))
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
shawn modersohn
Feb 11, 2004, 4:54:03 PM2/11/04
to
"Frank Kabel" <frank...@freenet.de> wrote in message
news:OBs9nVO8...@tk2msftngp13.phx.gbl...
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.
Frank Kabel
Feb 11, 2004, 5:49:23 PM2/11/04
to
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
as you are starting in row 2, adapt the formula:
=INDEX(B2:B999,MAX(IF(B2:B999>0,ROW(B2:B999),0))-1)
Frank
shawn modersohn
Feb 11, 2004, 6:02:51 PM2/11/04
to
Of course, thank you very much.
"Frank Kabel" <frank...@freenet.de> wrote in message
news:%23UTmUFP...@TK2MSFTNGP09.phx.gbl...
0 new messages