Count Zeros in cells before first number

P

pgc01

MrExcel MVP

Joined

Apr 25, 2006

Messages

19,897

• Mar 30, 2011

• #2

Hi

Try:

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

 

Upvote 0

T. Valko

Well-known Member

Joined

May 9, 2009

Messages

16,623

• Mar 30, 2011

• #3

kgkev said:

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.

Click to expand...

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.

 

Upvote 0

M

Matty

Well-known Member

Joined

Feb 17, 2007

Messages

3,717

• Mar 30, 2011

• #4

Yet another:

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

Array entered...

Matty

 

Upvote 0

K

kgkev

Well-known Member

Joined

Jun 24, 2008

Messages

1,291

Office Version

1. 365

Platform

1. Windows

• Mar 31, 2011

• #5

Thanks - Thats spot on!

 

Upvote 0