Retun greatest # of cells between nonzero
 

OK, I think I've seen this before, but I can't recall how it's done. So, if I
have a table with dates as headers, and a particular row may have, say:


0 0 0 1 0 0 0 0 0 0 2 0 0 1 0 0 0 4 0 0 0 0 0 1

As individual values.


I would like a formula that tells me that the longest time frame between
non-zero fields was 6 days. The actual value of the nonzero cells can vary.

Retun greatest # of cells between nonzero
 

Lets say your data is in row #1. In A2 enter:
=IF(A1=0,1,0)

In B2 enter:
=IF(B1=0,A2+1,0) and copy across.

We see:
0 0 0 1 0 0 0 0 0 0 2 0 0 1 0 0 0 4 0 0 0 0 0 1
1 2 3 0 1 2 3 4 5 6 0 1 2 0 1 2 3 0 1 2 3 4 5 0

Now all we need is:
=MAX(2:2)

--
Gary''s Student - gsnu200772


"Sean Timmons" wrote:

OK, I think I've seen this before, but I can't recall how it's done. So, if I
have a table with dates as headers, and a particular row may have, say:


0 0 0 1 0 0 0 0 0 0 2 0 0 1 0 0 0 4 0 0 0 0 0 1

As individual values.


I would like a formula that tells me that the longest time frame between
non-zero fields was 6 days. The actual value of the nonzero cells can vary.

Retun greatest # of cells between nonzero
 

That's waht I was looking for! Thank you!

"Gary''s Student" wrote:

Lets say your data is in row #1. In A2 enter:
=IF(A1=0,1,0)

In B2 enter:
=IF(B1=0,A2+1,0) and copy across.

We see:
0 0 0 1 0 0 0 0 0 0 2 0 0 1 0 0 0 4 0 0 0 0 0 1
1 2 3 0 1 2 3 4 5 6 0 1 2 0 1 2 3 0 1 2 3 4 5 0

Now all we need is:
=MAX(2:2)

--
Gary''s Student - gsnu200772


"Sean Timmons" wrote:

OK, I think I've seen this before, but I can't recall how it's done. So, if I
have a table with dates as headers, and a particular row may have, say:


0 0 0 1 0 0 0 0 0 0 2 0 0 1 0 0 0 4 0 0 0 0 0 1

As individual values.


I would like a formula that tells me that the longest time frame between
non-zero fields was 6 days. The actual value of the nonzero cells can vary.

Retun greatest # of cells between nonzero
 

Try this array formula** :

=MAX(FREQUENCY(IF(A2:X2=0,COLUMN(A2:X2)),IF(A2:X2< 0,COLUMN(A2:X2))))

Note that empty cells will be evaluated as 0.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"Sean Timmons" wrote in message
...
OK, I think I've seen this before, but I can't recall how it's done. So,
if I
have a table with dates as headers, and a particular row may have, say:


0 0 0 1 0 0 0 0 0 0 2 0 0 1 0 0 0 4 0 0 0 0 0 1

As individual values.


I would like a formula that tells me that the longest time frame between
non-zero fields was 6 days. The actual value of the nonzero cells can
vary.