Count Zeros in cells before first number

[kgkev]

In Q6-AD6 I have stock usage for a particular item

Q6 is 2011
R6 is 2010
.
.
AD6 is 1998

I would like to add a column to "last Used"

So if my data looks like

0,0,0,0,0,6,1,4,0,0,5,10,4,6

I can say that it was last used in 2006

Alternatively I can have it output last used 5 Years ago.

 

[pgc01]

Hi

Try:

=2012-MATCH(TRUE,INDEX(Q6:AD6>0,0),0)

 

[T. Valko]

In Q6-AD6 I have stock usage for a particular item

Q6 is 2011
R6 is 2010
.
.
AD6 is 1998

I would like to add a column to "last Used"

So if my data looks like

0,0,0,0,0,6,1,4,0,0,5,10,4,6

I can say that it was last used in 2006

Alternatively I can have it output last used 5 Years ago.

Here's another one...

Let's assume this data is in the range Q7:AD7...

0,0,0,0,0,6,1,4,0,0,5,10,4,6

Array entered**:

=INDEX(Q6:AD6,MATCH(1,IF(Q7:AD7>0,1),0))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

 

[Matty]

Yet another:

=INDEX(Q6:AD6,MATCH(TRUE,Q7:AD7>0,0))

Array entered...

Matty

 

[kgkev]

Thanks - Thats spot on!