Find last non-zero cell in a range

[FinController]

Hi there! Hope you can help me with this one:

I have a sheet with years and accounts with data.
I want to find the year in which the last non-zero value occurs.

Example:
2004 2005 2006 2007 2008 2009 2010
0 100 150 70 20 0 0

The last year with data is 2008. Is there a way to get that using a formula
and without setting up a host of intermediate test rows or VBA loops? The
data is in fact feb by formula, and the cells up to 2010 are never really
empty.

Thanks for any ideas.

Robin

 

[Dave R.]

=MAX(IF(C5:G5<>0,C4:G4))

adjust to your range, C5:G5 contain the values, C4:G4 contain the years.

enter the formula with CTRL SHIFT and ENTER at once, not just enter.

 

[Frank Haverkamp]

I don't know how much data you have or the exact nature of what you want
to do once you find this data, but...

I took your sample data copied it, paste special(Transpose) and then
sorted by year(Desending), auto filter(custom[greater than 0]. The last
year of data is show. You would need to custom filter each column off
and on but it may help.

Hope this helps some if not maybe a file that I can look at and some
idea what you want to do with the data once you find it.

Frank

Date Bob Bill Betty
2012 0 0 0
2011 0 0 0
2010 0 0 0
2009 0 60 0
2008 20 50 0
2007 70 40 50
2006 150 30 2
2005 100 20 3
2004 1 10 2

 

[Frank Kabel]

Hi Robin
if your years are in A1:G1 and the values in A2:G2 you may use the
following array formula (entered with CTRL+SHIFT+ENTER):
=INDEX(A1:G1,1,MAX(IF(A2:G2<>0,ROW(A2:G2))))

 

[FinController]

Both Dave and Frank Kabel have got it right as far as I am concerned, as I
cannot sort or move the data that happens to be part of a larger spreadsheet
model.

I just had to use COLUMN instead of ROW in Frank's solution. Also Frank's
formula returns the first year without a measure, while Dave's returns the
year with the last non-zero measure (and it's shorter).

I understand their formulas are based on the same key logic which is to use
the fact that the years are sorted in ascending order and that the highest
value of year with a non-zero measure is the year with the last measure.
That seems so simple once you have the solution...

Excel will never cease to suprise me!
Thanks for your help!

Robin

 

[Dave R.]

Close, but actually mine didn't depend on sorting of any kind (I didnt look
close at Frank's but I wouldn't think his would either). The years could be
laid out 1999 2001 1975 2004

and it would still return the max year value when the corresponding row was
not zero.

 

[Aladin Akyurek]

and it would still return the max year value when the corresponding row
was

not zero.

I don't think so. The OP is right: If years were not in ascending order, the
MAX formula you proposed would get it wrong for the OP wants the whatever
associated with the last non-zero value.

 

[Dave R.]

UR right, I misinterpreted the question as it was stated after taking into
account the example data. I read it to mean the highest year with nonzero
value, which (apparently) works for her.

 

[Frank Kabel]

Hi
yes I made the mistake of using ROW instead of COLUMN - but you saw it
fortunately
As opposite to Dave's solution my formula does not required a sorted
date range (by the use of INDEX in combination with the max column
number)

 

[Aladin Akyurek]

As long as year values are in ascending order, the MAX formula Dave proposed
will produce the intended result. Otherwise, the following would a bit more
efficient:

=LOOKUP(9.99999999999999E+307,IF(A2:G2,A2:G2,""),A1:G1)

which must be confirmed with control+shift+enter instead of just with enter.

 

[FinController]

Frank, I also have to deduct one form the COLUMN, otherwise the INDEX goes
out of range if the last year of the measure is also the last year in the
range. So the formula now becomes something like
=INDEX(B1:L1;1;MAX(IF(B2:L2<>0;COLUMN(B2:L2)-1)))
In fact I realize that this depends on where the range starts... mine was
starting in B which is colmun 2, so in fact the generic approach would be
=INDEX(B3:L3;1;MAX(IF(B4:L4<>0;COLUMN(B4:L4)-COLUMN(B4)+1)))... Tricky but
it works!

Testing this out a bit, it appears that Frank's solution will always return
the last year there is a measure in the range, whatever the order of the
years in the range.
Dave's formula will alwys return the latest year with a non-zero measure.
If the range is ordered by year, then the two formulas return the same year,
and I would then prefer Dave's for being the shorter one.

Thanks for all your input.

Robin