Last Value Greater Than Zero
 

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

Last Value Greater Than Zero
 

Here is one way...

=INDEX(A1:A1000,SUMPRODUCT(MAX((A1:A1000<0)*ROW(A 1: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


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

Last Value Greater Than Zero
 

=LOOKUP(2,1/(A1:A10000),A1:A1000)

--
HTH

Bob

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

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

Last Value Greater Than Zero
 

Another one:

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

--
Biff
Microsoft Excel MVP


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

Last Value Greater Than Zero
 

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

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
Here is one way...

=INDEX(A1:A1000,SUMPRODUCT(MAX((A1:A1000<0)*ROW(A 1: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


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

Last Value Greater Than Zero
 

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


"Bob Phillips" wrote in message
...
=LOOKUP(2,1/(A1:A10000),A1:A1000)

--
HTH

Bob

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

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

Last Value Greater Than Zero
 

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


"T. Valko" wrote in message
...
Another one:

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

--
Biff
Microsoft Excel MVP


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

Last Value Greater Than Zero
 

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


--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
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


"T. Valko" wrote in message
...
Another one:

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

--
Biff
Microsoft Excel MVP


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