HOW? Find and display the last cell in a column with a value > 0

C

Conker10382

I want to create a formula in a cell that will make that cell display the
value of the last cell in a column with a value greater than 0.
i.e.:
A B C D E
1 500 <----
2
3 17
4 75
5 500 <----
 
P

PMPLookingForToBe

Conker10382:

I'm not clear about your doubt. Do you mean that the 500 value is the
maximum in the range c1:c4?

Or, maybe the 500 value is on the first row of the range?

Please, could you please be more explicit?
 
B

Biff

Hi!

Try one of these:

If the range will ONLY CONTAIN NUMBERS (empty cells are ok) and no TEXT
including formula blanks:

=LOOKUP(2,1/(C2:C25>0),C2:C25)

Or, if there might be TEXT entries, entered as an array using the key
combination of CTRL,SHIFT,ENTER:

=INDEX(C2:C25,MAX(IF((ISNUMBER(C2:C25))*(C2:C25>0),ROW(C2:C25)-ROW(C2)+1)))

Biff
 
C

Conker10382

Sorry, I know my example leaves a bit to be desired, LOL. It's sort of hard
to explain, maybe this will help.

I am trying to create a spreadsheet that will keep a running total in a
colum. Each time the total is added to or subtracted from, the new total is
placed below the old total. I want the topmost cell in the column to display
the grand total (the last cell in the column greater than zero. See if this
example helps a bit more:
A B C
1 add/Sub Total
2 50 <---

3 100
4 -50 50 <---
5
6
If the total is changed, it then becomes:
A B C
1 add/Sub Total
2 40 <----

3 100
4 -50 50
5 -10 40 <----
6
Is that easier to understand? Each time the total is added to or subtracted
from, the new grand total should show at the topmost cell. I'm pretty sure
there is a formula that will search the entire colum for the last cell with a
value greater than zero and display that value, but I'm just not sure what it
is or how to use it.
 
R

Ragdyer

You're confusing me Biff.<g>

Why the array suggestion?

First one works fine for text *AND/OR* numbers, though it *also* returns
nulls, which of course can be very confusing if displaying an 'empty'
appearing cell.
 
D

Dav

biff's last formula entered as an array shift cntrl enter works, I was
going to suggest something similar

=INDEX(C2:C25,MAX(IF((ISNUMBER(C2:C25))*(C2:C25>0),ROW(C2:C25)-ROW(C2)+1)))

it assumes your data is in columnc the range being c2:c25 you may need
to extend the range for your purposes

Regards

Dav
 
B

Biff

Why the array suggestion?

It eliminates text including formula blanks.

The first formula won't.

The way things go for me is, I'd post only the first formula then the OP
would follow-up with " it doesn't work, I get a blank cell ???"

So, I just tried to cover all the bases without getting into overkill!

Biff
 
B

Biff

biff's last formula entered as an array shift cntrl enter works

Well, of course it works! <vbg>

They always work based on the questions and information provided! But of
course sometimes the poster forgets to mention some other details that casue
problems but we can deal with that in a follow-up!

Biff
 
C

Conker10382

Well, Biff I'm glad you posed both of them.. You were absolutly right. The
first one kept coming up with a blank cell, and the Index worked great.
Thanks!
 
R

Ragdyer

Well, we could just add another argument to by-pass the nulls, thus
*eliminating* any return of a *blank* cell:

=LOOKUP(2,1/((A1:A100<>"")*(A1:A100>0)),A1:A100)

This still returns text, which I think is OK, since in this scenario there
should be none, and in others, it could come in handy for an "all-around"
general last cell finder (with the >0, and <>"" qualifications).

Plus of course, it's non-array, but let's not get into that discussion.<bg>
 
B

Biff

You're welcome. Thanks for the feedback!

Biff

Conker10382 said:
Well, Biff I'm glad you posed both of them.. You were absolutly right.
The
first one kept coming up with a blank cell, and the Index worked great.
Thanks!
Click to expand...
 
Top