| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| if greater than? | Excel Worksheet Functions | |||
| If sum greater than 12 use 12 | Excel Discussion (Misc queries) | |||
| if value is greater than # * by 20% if less * by 40 | New Users to Excel | |||
| Greater Than but Less than | Excel Worksheet Functions | |||
| IF greater than...IF less than....====== | Excel Discussion (Misc queries) | |||
Hybrid Mode
