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 <----

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?


--
Gaspar
PM --Adopting methodology--


"Conker10382" wrote:

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 <----

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 mo
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.

"PMPLookingForToBe" wrote:

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?


--
Gaspar
PM --Adopting methodology--


"Conker10382" wrote:

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 <----

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:C250),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:C250) ,ROW(C2:C25)-ROW(C2)+1)))

Biff

"Conker10382" wrote in message
...
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 <----

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.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Biff" wrote in message
...
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:C250),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:C250) ,ROW(C2:C25)-ROW(C2)+1)))

Biff

"Conker10382" wrote in message
...
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 <----

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:C250) ,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


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=563825

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

"Dav" wrote in message
...

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:C250) ,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


--
Dav
------------------------------------------------------------------------
Dav's Profile:
http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=563825

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

"Ragdyer" wrote in message
...
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.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Biff" wrote in message
...
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:C250),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:C250) ,ROW(C2:C25)-ROW(C2)+1)))

Biff

"Conker10382" wrote in message
...
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 <----

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!

"Biff" wrote:

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

"Ragdyer" wrote in message
...
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.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Biff" wrote in message
...
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:C250),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:C250) ,ROW(C2:C25)-ROW(C2)+1)))

Biff

"Conker10382" wrote in message
...
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 <----

You're welcome. Thanks for the feedback!

Biff

"Conker10382" wrote in message
...
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!

"Biff" wrote:

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

"Ragdyer" wrote in message
...
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.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit
!
---------------------------------------------------------------------------
"Biff" wrote in message
...
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:C250),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:C250) ,ROW(C2:C25)-ROW(C2)+1)))

Biff

"Conker10382" wrote in message
...
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 <----

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:A1000)),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
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Biff" wrote in message
...
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

"Ragdyer" wrote in message
...
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.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Biff" wrote in message
...
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:C250),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:C250) ,ROW(C2:C25)-ROW(C2)+1)))

Biff

"Conker10382" wrote in message
...
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 <----