I need a formula to give me the maximum times the sequence, [ X (any value) Y], appears before another set of values that contains for example [ X (any value) A], [X (any value) B], [X (any value) C], etc. So the first value in that sequence HAS to be Xin order to break the streak. So sequences such as [T (any value) B], [A (any value) C], etc are to be ignored. I an currently using 2007 version of excel.
The array formula I was attempting to accomplish this, although it does execute, it returns the incorrect value of 1.
=MAX(FREQUENCY(IF(((AZ1:AZ38="x")*--(OFFSET(AZ1,2,0,ROWS(AZ1:AZ38))="y")),ROW(AZ1:AZ38)),IF(((AZ1:AZ38<>"x")*--(OFFSET(AZ1,2,0,ROWS(AZ1:AZ38))<>"y")),ROW(AZ1:AZ38))))
Here are a few examples of what value it should return based on various ranges with the column below:
Evaluating lines 1-38 the max frequency would be (3) both lines 1-10 & 32-38
(Note: The max would have been 8 if not for lines 11-13 & 25-27 breaking the streak.)
Evaluating lines 1-21 the max frequency would be also be (3) lines 1-10
(Note: The max would have been 5 if not for lines 11-13 breaking the streak.)
Evaluating lines 14-38 the max frequency would be (3) lines 32-38
(Note: The max would have been 5 if not for the lines 25-27 breaking the streak.)
Evaluating lines 11-24 the max frequency would be (2) lines 14-21
Evaluating lines 34-38 the max frequency would be (2)
1) X
2) A
3) Y
4) J
5) X
6) B
7) Y
8) X
9) C
10) Y
11) X
12) D
13) A
14) X
15) E
16) Y
17) B
18 ) N
19) X
20) V
21)Y
22) T
23) A
24) B
25) X
26) A
27) B
28) A
29) N
30) C
31) D
32) X
33) T
34) Y
35) X
36) X
37) Y
38) Y
So if anyone could provide a little input in regards to an alternative formula that would work or adjust the formula I provided, it would be greatly appreciated.
Reply With Quote