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

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

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

"Conker10382" <[email protected]> wrote in message
news:[email protected]...
>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 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.

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> "Conker10382" <[email protected]> wrote in message
> news:[email protected]...
>>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: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

>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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>> "Conker10382" <[email protected]> wrote in message
>> news:[email protected]...
>>>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

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> Dav
> ------------------------------------------------------------------------
> Dav's Profile:
> http://www.excelforum.com/member.php...o&userid=27107
> View this thread: http://www.excelforum.com/showthread...hreadid=563825
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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
> >>
> >> "Conker10382" <[email protected]> wrote in message
> >> news:[email protected]...
> >>>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: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>
--
Regards,

RD

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

"Biff" <[email protected]> wrote in message
news:[email protected]...
> >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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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
>>>
>>> "Conker10382" <[email protected]> wrote in message
>>> news:[email protected]...
>>>>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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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
>> >>
>> >> "Conker10382" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >>>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 <----
>> >>
>> >>
>> >
>>
>>
>>