Formula to determine average wait time for a number to re-appear

[D_Spark]

I need assistance in creating a formula to determine the average wait time for numbers greater than or equal to 200000 to appear in a series.

I have 100 lines of data (BJ100:BJ200)

The wait time will be determine by the row number,eg

BJ100=12
BJ101=200000
BJ102=15000
BJ103=200050
BJ104=9
BJ105=8
BJ106=5555
BJ107=300000
BJ108=0
BJ109=5000000
BJ110=250
BJ111=18
BJ112=0
BJ113=5555
BJ114=300000
BJ115=0
BJ116=250
BJ117=250
BJ118=18
BJ119=5000000




So for the above the expected return will be: the average of (2,4,2,5,5)

 

[AlanY]

try

Book1
	A	B	C	D
1
2	BJ100	12	0
3	BJ101	200000	2
4	BJ102	15000	0
5	BJ103	200050	4	2
6	BJ104	9	0
7	BJ105	8	0
8	BJ106	5555	0
9	BJ107	300000	8	4
10	BJ108	0	0
11	BJ109	5000000	10	2
12	BJ110	250	0
13	BJ111	18	0
14	BJ112	0	0
15	BJ113	5555	0
16	BJ114	300000	15	5
17	BJ115	0	0
18	BJ116	250	0
19	BJ117	250	0
20	BJ118	18	0
21	BJ119	5000000	20	5
22
23				3.6
Sheet3

Cell Formulas
Range	Formula
C2	=IF(B2>=200000,ROW(A2)-ROW($A$2)+1,0)
D2	=IFERROR(IF(C2>0,C2-LOOKUP(2,1/($C1:C$2>0),$C1:C$2),""),"")
D23	=AVERAGE(D2:D21)

 

[D_Spark]

Great thanks for your assistance

 

[AlanY]

you're welcome