Last Value Greater Than Zero

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Anto111 - 31 May 2008 08:47 GMT

Hi Guys,

I need to find the last value within a column range that is greater than zero.

For example, Within A1:A8 I have values of 1,0,3,5,6,0,0,0 I therefore need
excel to recognise the number 6 and ignore the subsequent 3 zero's.

I also need this to update with new data, so for example If A9 is zero then
continue to recognise the last value as 6, but if A9 is 2 then recognise the
most recent value as 2.

Sorry if the example is not clear.

Kind regards,

Ant

Reply to this Message

Rick Rothstein (MVP - VB) - 31 May 2008 09:21 GMT

Here is one way...

=INDEX(A1:A1000,SUMPRODUCT(MAX((A1:A1000<>0)*ROW(A1:A1000))))

Change the A1000 reference to a row reference higher than the maximum row
you ever expect to make use of in Column A.

Rick

> Hi Guys,
>
[quoted text clipped - 16 lines]
>
> Ant

Reply to this Message

Rick Rothstein (MVP - VB) - 31 May 2008 18:28 GMT

Based on the other two responses you got, it looks like I came up with the
*hard* way to do this.<g>

Rick

> Here is one way...
>
[quoted text clipped - 25 lines]
>>
>> Ant

Reply to this Message

Bob Phillips - 31 May 2008 10:00 GMT

=LOOKUP(2,1/(A1:A1000>0),A1:A1000)

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Hi Guys,
>
[quoted text clipped - 16 lines]
>
> Ant

Reply to this Message

Rick Rothstein (MVP - VB) - 31 May 2008 18:32 GMT

On the off chance that there could be negative values in the column, you
might consider changing your formula to this...

=LOOKUP(2,1/(A1:A1000<>0),A1:A1000)

Out of curiosity, is there an efficiency advantage in using a limited range
for the third argument to the LOOKUP function rather than using a whole
column reference like this formula?

=LOOKUP(2,1/(A1:A1000<>0),A:A)

Rick

> =LOOKUP(2,1/(A1:A1000>0),A1:A1000)
>
[quoted text clipped - 18 lines]
>>
>> Ant

Reply to this Message

T. Valko - 31 May 2008 18:18 GMT

Another one:

=LOOKUP(1E100,1/A1:A15,A1:A15)

Signature

Biff
Microsoft Excel MVP

> Hi Guys,
>
[quoted text clipped - 16 lines]
>
> Ant

Reply to this Message

Rick Rothstein (MVP - VB) - 31 May 2008 18:34 GMT

Out of curiosity, is there an efficiency advantage in using a limited range
for the third argument to the LOOKUP function rather than using a whole
column reference like in this formula?

=LOOKUP(1E+100,1/A1:A15,A:A)

Rick

> Another one:
>
[quoted text clipped - 20 lines]
>>
>> Ant

Reply to this Message

T. Valko - 31 May 2008 18:56 GMT

=LOOKUP(1E+100,1/A1:A15,A:A)
=LOOKUP(1E+100,1/A1:A15,A1:A15)

Both have virtually identical calc times.

You just can't use an entire column to *calculate* the lookup_vector in
versions prior to Excel 2007. (full column array rule)

Signature

Biff
Microsoft Excel MVP

> Out of curiosity, is there an efficiency advantage in using a limited
> range for the third argument to the LOOKUP function rather than using a
[quoted text clipped - 28 lines]
>>>
>>> Ant

Reply to this Message