Excel: Last non zero value in a row

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
 
H

Harlan Grove

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

Or you could return that bottommost value using the regular formula

=LOOKUP(2,1/(A3:A10000>0),A3:A10000)
 
R

Ron Rosenfeld

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top