Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel: Last non zero value in a row
I need to (in formula not VB code) lookup the last non zero value in a row
and return the "header record value" for that column e.g below - I want to see 27/10 for the first row and 10/11 for the second row. Date 27/10/2008 03/11/2008 10/11/2008 27/10/2008 20.5 0 0 10/11/2008 0 $0 $10 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel: Last non zero value in a row
SimmoG wrote...
I need to (in formula not VB code) lookup the last non zero value in a row .... I'll assume you want to find the last positive numeric value. If you could have positive or negative values, it's likely you could also have legitimate zero values. The row index of the last nonzero value in, say, A3:A10000 could be found using the array formula =MATCH2,1/(A3:A100000)) Or you could return that bottommost value using the regular formula =LOOKUP(2,1/(A3:A100000),A3:A10000) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel: Last non zero value in a row
On Thu, 14 May 2009 07:37:02 -0700, SimmoG
wrote: I need to (in formula not VB code) lookup the last non zero value in a row and return the "header record value" for that column e.g below - I want to see 27/10 for the first row and 10/11 for the second row. Date 27/10/2008 03/11/2008 10/11/2008 27/10/2008 20.5 0 0 10/11/2008 0 $0 $10 =LOOKUP(2,1/(B2:Z2<0),$B$1:$Z$1) This assumes your headers are in B1:Z1 your data is below that and you want this formula in A2 (for row 2) Fill down for subsequent rows. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|