Help with formula

Hi,

Enter below formula in N3 & copy down.

=IF(SUM(B3:M3)>=3,"Flag Account","")

Raj

MrRajKumar said:

Hi,

Enter below formula in N3 & copy down.

=IF(SUM(B3:M3)>=3,"Flag Account","")

Raj

Click to expand...

Thanks, but I'm looking for it to only flag the account when there are 3 or more consecutive cells in a row with the value 1. The total cells with that value are not important, emphasis is on the consecutive ones.

The 1's represent accounts that are in arrears for a certain month so I want to flag accounts that stay in arrears for 3 consecutive months or more. Accounts that go into and then out of arrears soon after are not of concern here.

2 ways:
in N3, enter this laborious formula:
or, ARRAY-ENTER (ctrl+shft+Enter) this shorter, more flexible but incomprehensible formula:
Either way, copy down.

p45cal said:

2 ways:
in N3, enter this laborious formula:

Code:

=IF(SUM(SUM(B3:D3)=3,SUM(C3:E3)=3,SUM(D3:F3)=3,SUM(E3:G3)=3,SUM(F3:H3)=3,SUM(G3:I3)=3,SUM(H3:J3)=3,SUM(I3:K3)=3,SUM(J3:L3)=3,SUM(K3:M3)=3),"flag account","")

or, ARRAY-ENTER (ctrl+shft+Enter) this shorter, more flexible but incomprehensible formula:

Code:

= IF(SUM(IF(FREQUENCY(IF(B3:M3=1,COLUMN(B3:M3)),IF(B3:M3<>1,COLUMN(B3:M3)))>=3,1))>0,"flag account","")

Either way, copy down.

Click to expand...

Thanks, that works well.

Try this...

=IF(SUMPRODUCT(--(B1:K1=1),--(C1:L1=1),--(D1:M1=1)),"Flag","")

Note how the ranges are offset.

p45cal said:

ARRAY-ENTER (ctrl+shft+Enter) this shorter, more flexible but incomprehensible formula:

Code:

= IF(SUM(IF(FREQUENCY(IF(B3:M3=1,COLUMN(B3:M3)),IF(B3:M3<>1,COLUMN(B3:M3)))>=3,1))>0,"flag account","")

Either way, copy down.

Click to expand...

It's not too bad!

Actually, it's quite simple if you understand how FREQUENCY works.

I don't have time right now to explain how it works but I'll check back sometime tonight (my time!).

#NAME? said:

Try this...

=IF(SUMPRODUCT(--(B1:K1=1),--(C1:L1=1),--(D1:M1=1)),"Flag","")

Note how the ranges are offset.

Click to expand...

which, academically speaking and just in this particular case, can be shortened further by an array-entered:

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","")

Click to expand...

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?