Try something like this:

Using your data in A1:B30
E1:
=INDEX(A:A,SMALL(IF(($B$2:$B$31=1)*($B$1:$B$30=0), ROW($B$2:$B$31)),ROW()+1))-INDEX(A:A,SMALL(IF(($B$2:$B$31=1)*($B$1:$B$30=0),R OW($B$2:$B$31)),ROW()))

Note: Commit that array formula by holding down the [Ctrl][Shift] keys and
press [Enter].

That formula returns the first interval .
Copy that formula into E2 and down as far as you need for the 2nd, 3rd, etc
intervals.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"John" wrote:

I am trying to calculate the average time between starting 1's. The data has
a date which is every day and a 1 or 0 next to it. When the data goes from 0
to 1 I want to start counting until I find the next 1 that follows a 0. Once
I have all those days between starting 1's I want to find the average days
between starting 1's. I have a dataset that looks like the following:

08/04/05 0
08/05/05 1
08/06/05 1
08/07/05 1
08/08/05 1
08/09/05 0
08/10/05 0
08/11/05 0
08/12/05 0
08/13/05 0
08/14/05 1
08/15/05 1
08/16/05 1
08/17/05 0
08/18/05 0
08/19/05 0
08/20/05 0
08/21/05 0
08/22/05 0
08/23/05 1
08/24/05 1
08/25/05 1
08/26/05 1
08/27/05 0
08/28/05 0
08/29/05 0
08/30/05 0
08/31/05 1
09/01/05 1
09/02/05 0

So for the above example the days between starting 1's is 9,9, and 8. The
average would be (9+9+8)/3 = 8.67.

Is there a way to get the above using a Formula or would it need to be done
in VBA code?

Best regards,
John




---
avast! Antivirus: Outbound message clean.
Virus Database (VPS): 0612-4, 03/25/2006
Tested on: 3/27/2006 8:02:02 AM
avast! - copyright (c) 1988-2005 ALWIL Software.
http://www.avast.com