PDA

View Full Version : Max Frequency



Dunbar
08-04-2014, 10:25 PM
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 X in 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.

lecxe
08-14-2014, 06:52 PM
Hi Dunbar
Welcome to the board

If I understood correctly, try this array formula:

=MAX(FREQUENCY(IF(((AZ1:AZ36="x")*(AZ3:AZ38="y")),ROW(AZ1:AZ36)),IF(((AZ1:AZ38="x")*(AZ3:AZ38<>"y")),ROW(AZ1:AZ38))))



Remark: Posts all in bold are disagreeable to read. I'd save the bold to emphasize something specific.