Excel: Last non zero value in a row

  • Thread starter Thread starter SimmoG
  • Start date Start date
S

SimmoG

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
 
SimmoG said:
I need to (in formula not VB code) lookup the last non zero value in a row
Click to expand...
....

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:A10000>0))

Or you could return that bottommost value using the regular formula

=LOOKUP(2,1/(A3:A10000>0),A3:A10000)
 
SimmoG said:
I need to (in formula not VB code) lookup the last non zero value in a row
Click to expand...
....

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:A10000>0))

Or you could return that bottommost value using the regular formula

=LOOKUP(2,1/(A3:A10000>0),A3:A10000)
 
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
Click to expand...

=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
 
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
Click to expand...

=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
 
Back
Top