Ok, I got a bit side tracked but here's how this formula works.
= IF(SUM(IF(FREQUENCY(IF(B3:M3=1,COLUMN(B3:M3)),IF(B3:M3<>1,COLUMN(B3:M3)))>=3,1))>0,"flag account","")
If I were writing that formula I'd use some different logic. We want to know if there are 3 or more consecutive cells that contain 1.
In this case we can just test that the max number of consecutives is >=3.
=IF(MAX(FREQUENCY(IF(B3:M3=1,COLUMN(B3:M3)),IF(B3:M3<>1,COLUMN(B3:M3))))>=3,"flag account","")
Now let's see what's really happening here.
FREQUENCY returns an array of counts based on the numbers in the data array and the bns array.
Let's assume we have this data:
22
25
33
15
9
10
We want to get the frequencies (counts) using these bins:
10
20
30
The FREQUENCY function will return an array of counts based on these criteria:
count if data is <=10
count if data is >10 and <=20
count if data is >20 and <=30
count if data is >30
Text, logicals (TRUE, FALSE) and empty cells are ignored.
Based on the data the results of the counts would be:
count if data is <=10 =
2 (9 and 10)
count if data is >10 and <=20 =
1 (15)
count if data is >20 and <=30 =
2 (22 and 25)
count if data is >30 =
1 (33)
In the formula:
=IF(MAX(FREQUENCY(IF(B3:M3=1,COLUMN(B3:M3)),IF(B3:M3<>1,COLUMN(B3:M3))))>=3,"flag account","")
We're using IF statements to generate the data array and the bins array.
Let's use this smaller data sample to see how it works. Assume the range is B3:H3 and contains these entries.
0 0 0 1 1 1 0
So, to get the data array we use:
IF(B3:H3=1,COLUMN(B3:H3))
If B3:H3 equals 1 return the cell column number otherwise, return FALSE. The returned array would be (F = FALSE):
F F F 5 6 7 F
To get the bins array we use:
IF(B3:H3<>1,COLUMN(B3:H3))
If B3:H3 is not equal to 1 return the cell column numbers otherwise, return FALSE. The returned array would be:
2 3 4 F F F 8
So, FREQUENCY now performs the counts using these values.
Data array: 5 6 7
Bins array: 2 3 4 8
count if data is <=2
count if data is >2 and <=3
count if data is >3 and <=4
count if data is >4 and <=8
count if data is >8
Based on the data the results of the counts would be:
count if data is <=2 =
0
count if data is >2 and <=3 =
0
count if data is >3 and <=4 =
0
count if data is >4 and <=8 =
3 (5, 6 and 7)
count if data is >8 =
0
Now that we have the frequencies we just test that the max frequency is >=3:
=IF(MAX({0;0;0;3;0})>=3,"flag account","")
=IF(3>=3,"flag account","")
3 is >=3 = TRUE so:
=IF(MAX(FREQUENCY(IF(B3:H3=1,COLUMN(B3:H3)),IF(B3:H3<>1,COLUMN(B3:H3))))>=3,"flag account","")
= flag account
That wasn't so bad was it?