software

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.
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(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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

quot;Johnquot; gt; wrote in message
...
gt;I am trying to calculate the average time between starting 1's. The data
gt;has a date which is every day and a 1 or 0 next to it. When the data goes
gt;from 0 to 1 I want to start counting until I find the next 1 that follows a
gt;0. Once I have all those days between starting 1's I want to find the
gt;average days between starting 1's. I have a dataset that looks like the
gt;following:
gt;
gt; 08/04/05 0
gt; 08/05/05 1
gt; 08/06/05 1
gt; 08/07/05 1
gt; 08/08/05 1
gt; 08/09/05 0
gt; 08/10/05 0
gt; 08/11/05 0
gt; 08/12/05 0
gt; 08/13/05 0
gt; 08/14/05 1
gt; 08/15/05 1
gt; 08/16/05 1
gt; 08/17/05 0
gt; 08/18/05 0
gt; 08/19/05 0
gt; 08/20/05 0
gt; 08/21/05 0
gt; 08/22/05 0
gt; 08/23/05 1
gt; 08/24/05 1
gt; 08/25/05 1
gt; 08/26/05 1
gt; 08/27/05 0
gt; 08/28/05 0
gt; 08/29/05 0
gt; 08/30/05 0
gt; 08/31/05 1
gt; 09/01/05 1
gt; 09/02/05 0
gt;
gt; So for the above example the days between starting 1's is 9,9, and 8. The
gt; average would be (9 9 8)/3 = 8.67.
gt;
gt; Is there a way to get the above using a Formula or would it need to be
gt; done in VBA code?
gt;
gt; Best regards,
gt; John
gt;
gt;
gt;
gt; ---
gt; avast! Antivirus: Outbound message clean.
gt; Virus Database (VPS): 0612-4, 03/25/2006
gt; Tested on: 3/27/2006 8:02:02 AM
gt; avast! - copyright (c) 1988-2005 ALWIL Software.
gt; www.avast.com
gt;
gt;
gt;
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-Proquot;Johnquot; wrote:

gt; I am trying to calculate the average time between starting 1's. The data has
gt; a date which is every day and a 1 or 0 next to it. When the data goes from 0
gt; to 1 I want to start counting until I find the next 1 that follows a 0. Once
gt; I have all those days between starting 1's I want to find the average days
gt; between starting 1's. I have a dataset that looks like the following:
gt;
gt; 08/04/05 0
gt; 08/05/05 1
gt; 08/06/05 1
gt; 08/07/05 1
gt; 08/08/05 1
gt; 08/09/05 0
gt; 08/10/05 0
gt; 08/11/05 0
gt; 08/12/05 0
gt; 08/13/05 0
gt; 08/14/05 1
gt; 08/15/05 1
gt; 08/16/05 1
gt; 08/17/05 0
gt; 08/18/05 0
gt; 08/19/05 0
gt; 08/20/05 0
gt; 08/21/05 0
gt; 08/22/05 0
gt; 08/23/05 1
gt; 08/24/05 1
gt; 08/25/05 1
gt; 08/26/05 1
gt; 08/27/05 0
gt; 08/28/05 0
gt; 08/29/05 0
gt; 08/30/05 0
gt; 08/31/05 1
gt; 09/01/05 1
gt; 09/02/05 0
gt;
gt; So for the above example the days between starting 1's is 9,9, and 8. The
gt; average would be (9 9 8)/3 = 8.67.
gt;
gt; Is there a way to get the above using a Formula or would it need to be done
gt; in VBA code?
gt;
gt; Best regards,
gt; John
gt;
gt;
gt;
gt;
gt; ---
gt; avast! Antivirus: Outbound message clean.
gt; Virus Database (VPS): 0612-4, 03/25/2006
gt; Tested on: 3/27/2006 8:02:02 AM
gt; avast! - copyright (c) 1988-2005 ALWIL Software.
gt; www.avast.com
gt;
gt;
gt;
gt;