Count cycle period

[Kishan]

Hi,

I need to count cycle of 1X2 period when each time it is completed.

Data in Cells C6:C66 Result in cells E6:E14 colours are just shown to show example clearly.

Example data

Book1
	A	B	C	D	E	F
1
2
3
4
5			C1		Count Cycle
6			X		5
7			X		12
8			X		13
9			2		7
10			1		5
11			X		7
12			X		3
13			1		4
14			1		5
15			1
16			X
17			1
18			1
19			X
20			1
21			1
22			2
23			1
24			X
25			1
26			X
27			1
28			1
29			1
30			1
31			1
32			1
33			2
34			1
35			2
36			1
37			X
38			X
39			X
40			1
41			X
42			2
43			X
44			X
45			1
46			1
47			2
48			1
49			X
50			X
51			X
52			X
53			X
54			2
55			1
56			X
57			2
58			1
59			1
60			2
61			X
62			2
63			1
64			1
65			1
66			X
67
68
Sheet1

Thank you in advance

Kishan

 

[Kishan]

I think you have Excel 2000? That doesn't have the IFERROR function. You can just remove it like this:

=MAX(MATCH("X",OFFSET($C$6:$C$66,SUM(E$5:E5),0),0),MATCH(1,OFFSET($C$6:$C$66,SUM(E$5:E5),0),0),MATCH(2,OFFSET($C$6:$C$66,SUM(E$5:E5),0),0))

I believe the rest of the functions work in 2000. You'll just get an error at the end of the list instead of an empty cell.

Hi Eric, Yes this formula is working perfect

Thank you very much for your help

Kind Regards,
Kishan