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

Calculating time between values (tricky Problem)
 

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" wrote in message
...
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

Calculating time between values (tricky Problem)
 

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

Calculating time between values (tricky Problem)
 

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" wrote in message
...
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" wrote in message
...
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