Calculating time between values (tricky Problem)

[John]

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 Liengme]

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(D228)+COUNTIF(D328,0))/COUNTIF(D328,0)
The numerator add the 1's and then adds 1 for every zero in the range AFTER
THE FIRST ZERO (so it is D328 not D228)
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

 

[Ron Coderre]

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]

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