Calculating time between values (tricky Problem)

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

Let the dates be in A1:A28 with the 1/0 in B1:B28.
I have ignored the last two data lines since we have hit the end of a
'cycle'

I got my answer with two helper columns; it could be done with one but two
makes for easier explanation.
In C2 (the SECOND row) use =AND(B1=0,B2=1) and copy down the column
Note the groups of FALSE, there are groups of 8, 8, 7.
Compare this to your values 9,9,8 (we are 1 out in each case)
As I want to do arithmetic I need to get numbers, so in D2 use =NOT(C2)*1
This gives 0 when C is TRUE and 1 when C is FALSE
We have groups of 8, 8, and 7 values of 1, each separated by 0
Now to average the data: we want to count the 1's but we need to add an
additional 1 for very group. Recall every group ends with a 0.
The average is given by =(SUM(D2:D28)+COUNTIF(D3:D28,0))/COUNTIF(D3:D28,0)
The numerator add the 1's and then adds 1 for every zero in the range AFTER
THE FIRST ZERO (so it is D3:D28 not D2:D28)
The COUNTIF also tells how many groups we have. So we get the average size
of each group.
I suspect there is a more sophisticated way but that's my contribution.
If I have time today I will try to code a VBA function
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"John" <[email protected]> wrote in message
news:%[email protected]...
>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
>
>
>

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),ROW($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
>
>
>
>

Bernard that worked great! If you do get a chance to
make a VBA function let me know as that would be a lot
easier and cleaner then trying to do the helper
columns. Thank you for all your help! I really
appreciate it.

Best regards,
John

"Bernard Liengme" <[email protected]> wrote in message
news:%[email protected]...
> Let the dates be in A1:A28 with the 1/0 in B1:B28.
> I have ignored the last two data lines since we have hit the end of a
> 'cycle'
>
> I got my answer with two helper columns; it could be done with one but two
> makes for easier explanation.
> In C2 (the SECOND row) use =AND(B1=0,B2=1) and copy down the column
> Note the groups of FALSE, there are groups of 8, 8, 7.
> Compare this to your values 9,9,8 (we are 1 out in each case)
> As I want to do arithmetic I need to get numbers, so in D2 use =NOT(C2)*1
> This gives 0 when C is TRUE and 1 when C is FALSE
> We have groups of 8, 8, and 7 values of 1, each separated by 0
> Now to average the data: we want to count the 1's but we need to add an
> additional 1 for very group. Recall every group ends with a 0.
> The average is given by =(SUM(D2:D28)+COUNTIF(D3:D28,0))/COUNTIF(D3:D28,0)
> The numerator add the 1's and then adds 1 for every zero in the range
> AFTER THE FIRST ZERO (so it is D3:D28 not D2:D28)
> The COUNTIF also tells how many groups we have. So we get the average size
> of each group.
> I suspect there is a more sophisticated way but that's my contribution.
> If I have time today I will try to code a VBA function
> best wishes
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "John" <[email protected]> wrote in message
> news:%[email protected]...
>>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
>>
>>
>>
>
>