Count if consecutuve missed payments only

[brandon16]

Hi
I have 4 columns and I need to know how many consecutive payments have been missed. The column titles are 'Missed or less payment received then 1</SPAN>'In the columns below there is a '1' if missed.

example
If in August there is a 1 and in Sep there is a 1 but October there is a 0 then i need to show that as 2 consecutive payments.
If in Auguest there is a 0 and in Sep there is a 0 and in October there is a 0 but November is a 1 then need to show 1 consecutive payment

Below is my table, any ideas on how to do this?

August</SPAN>

September</SPAN>

October</SPAN>

November</SPAN>

Payment received August</SPAN>

Arrears balance as at COB 31.08.14</SPAN>

Hidden Arrears</SPAN>

Missed or less payment received then 1</SPAN>

Payment Received September</SPAN>

Arrears balance as at COB 30.09.14</SPAN>

Hidden Arrears</SPAN>

Missed or less payment received then 1</SPAN>

Payment Received October</SPAN>

Arrears Balance as at COB 31.10.14</SPAN>

Hidden Arrears</SPAN>

Missed or less payment received then 1</SPAN>

Payment Received November</SPAN>

Arrears balance as at COB 30.11.14</SPAN>

Hidden Arrears</SPAN>

Missed or less payment received then 1</SPAN>

Difference from July - November</SPAN>

Volume of Accs</SPAN>

Total Number of Missed Payments</SPAN>

Total number of Consecutive Payments</SPAN>

Accounts in Credit as at November 2014</SPAN>

Full payment received</SPAN>

-£0.01</SPAN>

N</SPAN>

0</SPAN>

Less than full payment received</SPAN>

£0.00</SPAN>

1 </SPAN>

Full Payment received</SPAN>

£0.00</SPAN>

0 </SPAN>

Full Payment received</SPAN>

£0.00</SPAN>

0 </SPAN>

£0.01</SPAN>

1</SPAN>

1</SPAN>

Full payment received</SPAN>

-£0.01</SPAN>

N</SPAN>

0</SPAN>

Full Payment received</SPAN>

-£0.01</SPAN>

0 </SPAN>

Full Payment received</SPAN>

-£0.01</SPAN>

0 </SPAN>

Full Payment received</SPAN>

-£0.01</SPAN>

0 </SPAN>

£0.00</SPAN>

1</SPAN>

0</SPAN>

Full payment received</SPAN>

-£0.01</SPAN>

N</SPAN>

0</SPAN>

Full Payment received</SPAN>

-£0.01</SPAN>

0 </SPAN>

Full Payment received</SPAN>

-£0.01</SPAN>

0 </SPAN>

Full Payment received</SPAN>

-£0.01</SPAN>

0 </SPAN>

£0.00</SPAN>

1</SPAN>

0</SPAN>

Full payment received</SPAN>

-£0.01</SPAN>

N</SPAN>

0</SPAN>

Full Payment received</SPAN>

-£0.01</SPAN>

0 </SPAN>

Full Payment received</SPAN>

-£0.01</SPAN>

0 </SPAN>

Full Payment received</SPAN>

-£0.01</SPAN>

0 </SPAN>

£0.00</SPAN>

1</SPAN>

0</SPAN>

Full payment received</SPAN>

-£0.01</SPAN>

N</SPAN>

0</SPAN>

Full Payment received</SPAN>

-£0.01</SPAN>

0 </SPAN>

Full Payment received</SPAN>

-£0.01</SPAN>

0 </SPAN>

Full Payment received</SPAN>

-£0.01</SPAN>

0 </SPAN>

£0.00</SPAN>

1</SPAN>

0</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL span=3><COL><COL span=3><COL><COL span=3><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP>

 

[joemeshuggah]

i assumed the sequence was by row (e.g. january row2, february row 3, etc) when i tried this formula...it seemed to work; im sure you could use the same concept by altering it to work by column.

formula assumed payment indication was in column d, with "1" being paid and "0" being unpaid

=IF(D2<>0,0,IF(OR(D1=0,D2=0),E1+1,0))

 

[brandon16]

Sorry,

I need to measure it by column, so if cell J5 was 1 and cell N5 was 1 r5 was 0 and v5 was 0 then populate cell Z5 with 2. Then I can drag the formula down.
Cell J5
August
Missed or less payment received then 1

cell N5
September
Missed or less payment received then 1

cell R5
October
Missed or less payment received then 1

cell V5
November
Missed or less payment received then 1







</SPAN>

 

[joemeshuggah]

if it is always only 4 columns and you are only concerned with consecutive payments missed from the last month going backwards then this might work...
=IF(AND(J3=0,N3=0,R3=0,V3=0),4,IF(AND(N3=0,R3=0,V3=0),3,IF(AND(R3=0,V3=0),2,IF(V3=0,1,0))))

 

[brandon16]

I have added another column in as I forget but now it won't work, I have followed the same principle?

=IF(AND(G5=0,K5=0,O5=0,S5=0,W5=0),5,IF(AND(K5=0,O5=0,S5=0,W5=0),4,IF(AND(O5=0,S5=0,W5=0),3,IF(AND(S5=0,W5=0),2,IF(AND(W5=0),1,1,0))))

 

[brandon16]

Also, the formula above doesn't quite work. For the first row

August
Missed or less payment received then 1
Value = 0

cell N5
September
Missed or less payment received then 1
Value = 1

cell R5
October
Missed or less payment received then 1
Value = 0
cell V5
November
Missed or less payment received then 1
Value = 0
So the output should be 1

The row under shows the following and should output a 0 as im only looking at consecutive missed payments of which is the value of 1

August
Missed or less payment received then 1
Value = 0

cell N5
September
Missed or less payment received then 1
Value = 0

cell R5
October
Missed or less payment received then 1
Value = 0
cell V5
November
Missed or less payment received then 1
Value = 0

 

[joemeshuggah]

=if(and(g5=0,k5=0,o5=0,s5=0,w5=0),5,if(and(k5=0,o5=0,s5=0,w5=0),4,if(and(o5=0,s5=0,w5=0),3,if(and(s5=0,w5=0),2,if(w5 =0,1,0)))))

 

[joemeshuggah]

it would work only if you are concerned with consecutive payments missed from the last month going backwards as a reference. if you have 4 missed payments in a row, but the next month have a payment, it will not denote that there were 4 consecutive missed payments. are you looking for the maximum number of missed payments?

 

[brandon16]

In the row im looking for the number of consecutive missed payments, so where the cells G5 K5 O5 S5 W5 have a consecutive missed payment (i.e value of 1).

G5 - 1 K5 - 0 O5 -1 S5 -0 W5 - 0 - Thats 1 consecutive missed payments
G6 - 1 K6 - 1 O6 -1 S6 -0 W6 - 0 - Thats 3 consecutive missed payments

Is that not do able?

 

[joemeshuggah]

im sure it is, but probably not with a formula. you would probably need to build a custom function with vba