Customer Debtors tracker formula

[kevzmm]

Hi,

Im trying to add in some way of tracking debt of customers in a spreadsheet.

So far the formula say if a date is over 60 days then it will say Recourse =IFERROR(IF(O2>60,"RECOURSE"),0)

But i now need it to do the same thing track if an invoice is over 60 days but also if the customers debt is 50% over 60 days then all of the invoices are considered at recourse.

Sample data will look something like this;

[TABLE="width: 1567"]
<tbody>[TR]
[TD="class: xl69, width: 231"]Client[/TD]
[TD="class: xl67, width: 75"]Date [/TD]
[TD="class: xl67, width: 71"]Ref[/TD]
[TD="class: xl67, width: 238"]Details [/TD]
[TD="class: xl70, width: 104"] Balance[/TD]
[TD="class: xl70, width: 122"] Current[/TD]
[TD="class: xl70, width: 94"] P1[/TD]
[TD="class: xl70, width: 109"] P2[/TD]
[TD="class: xl70, width: 94"] P3[/TD]
[TD="class: xl71, width: 86"] Older[/TD]
[TD="class: xl72, width: 75"]Due Date[/TD]
[TD="class: xl68, width: 94"]Payment Terms[/TD]
[TD="class: xl73, width: 87"]Days Overdue[/TD]
[TD="class: xl73, width: 87"]Debt Status[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1567"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Customer 1[/TD]
[TD]30/04/2017[/TD]
[TD]1360946[/TD]
[TD]Description [/TD]
[TD] 1,000.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] 1,000.00[/TD]
[TD="align: right"]30-05-2017[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]160 [/TD]
[TD]RECOURSE[/TD]
[/TR]
[TR]
[TD]Customer 1[/TD]
[TD]24/05/2017[/TD]
[TD]1361088[/TD]
[TD]Description [/TD]
[TD] 1,900.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] 1,900.00[/TD]
[TD="align: right"]23-06-2017[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]136 [/TD]
[TD]RECOURSE[/TD]
[/TR]
[TR]
[TD]Customer 1[/TD]
[TD]10/05/2017[/TD]
[TD]1360988[/TD]
[TD]Description[/TD]
[TD] 1,200.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] 1,200.00[/TD]
[TD="align: right"]09-06-2017[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]150[/TD]
[TD] RECOURSE[/TD]
[/TR]
[TR]
[TD]Customer 1[/TD]
[TD]09/08/2017 [/TD]
[TD]1361681[/TD]
[TD]Description[/TD]
[TD] 1,000.00[/TD]
[TD] [/TD]
[TD] 1,000.00[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD="align: right"]08-09-2017[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]59[/TD]
[TD="align: center"]FALSE
[/TD]
[/TR]
</tbody>[/TABLE]


So here where it say False it should say Recourse as more than 50% of the total value of the invoices (column E) is over 60 days for that customer.

FYI there are various customers and this is updated everyday and has many customers in the spreadsheet, so it may need a look up for the customers rather than selecting the particular customer in the formula.

Also column M is the amount of days over due.

Thanks

 

[AlanY]

try this instead

=IFERROR(IF(OR(SUM(F2:J2)>0.5*E2,M2>60),"RECOURSE"),0)

this probably will work as well

IF(OR(SUM(F2:J2)>0.5*E2,M2>60),"RECOURSE",0)

 

[kevzmm]

Hi thanks for the reply,

this didnt seem to work, could your explain the cells, so i can add it and test it properly.

Thanks,

 

[AlanY]

I've modified your formula to suit, cells might be different

Excel 2013/2016
	A	E	F	G	H	I	J	K	L	M	N	O
1	Client	Balance	Current	P1	P2	P3	Older	Due Date	Payment Terms	Days Overdue	Debt Status
2	Customer 1	1,000.00					1,000.00	30/05/2017	30	160	RECOURSE	RECOURSE
3	Customer 1	1,900.00					1,900.00	23/06/2017	30	136	RECOURSE	RECOURSE
4	Customer 1	1,200.00					1,200.00	09/06/2017	30	150	RECOURSE	RECOURSE
5	Customer 1	1,000.00		1,000.00				08/09/2017	30	59	RECOURSE	RECOURSE
6	Customer 1	1,000.00			499			08/09/2017	30	45	FALSE	0
7	Customer 1	1,000.00			501			08/09/2017	30	45	RECOURSE	RECOURSE
Sheet4

Cell Formulas
Range	Formula
N2	=IFERROR(IF(OR(SUM(F2:J2)>0.5*E2,M2>60),"RECOURSE"),0)
O2	=IF(OR(SUM(F2:J2)>0.5*E2,M2>60),"RECOURSE",0)

 

[AlanY]

basically it test if the days overdue > 60 days, OR

the sum of Cols F to J is greater than half of balance (Col E)

 

[kevzmm]

Hi,


Its meant to be half of the recourse balance not the total balance

 

[kevzmm]

so it should say in this example that all of the invoices for this customer is in the recourse category as their balance in recourse is over 50%

 

[AlanY]

Hi,


Its meant to be half of the recourse balance not the total balance

could you give an example of the highlighted RED from one of the row what's required.

But i now need it to do the same thing track if an invoice is over 60 days but also if the customers debt is 50% over 60 days then all of the invoices are considered at recourse.

 

[kevzmm]

so in this situation with data i given above the recourse balance is £4100 & the remaining non recourse is £3,000, which is less than 50% of the total balance (£6,100) so everything for the customer should be in recourse.

So here this customer should show everything in recourse.

 

[AlanY]

so it should say in this example that all of the invoices for this customer is in the recourse category as their balance in recourse is over 50%

still not clear, do you mean something like this

Excel 2013/2016
	A	E	F	G	H	I	J	K	L	M	N	O
1	Client	Balance	Current	P1	P2	P3	Older	Due Date	Payment Terms	Days Overdue	Debt Status
2	Customer 1	1,000.00					1,000.00	30/05/2017	30	160	RECOURSE	RECOURSE
3	Customer 2	1,900.00					1,900.00	23/06/2017	30	136	RECOURSE	RECOURSE
4	Customer 3	1,200.00					1,200.00	09/06/2017	30	150	RECOURSE	RECOURSE
5	Customer 4	1,000.00		1,000.00				08/09/2017	30	59	RECOURSE	RECOURSE
6	Customer 5	499.00			1000			08/09/2017	30	45	FALSE	0
7	Customer 6	501.00			1000			08/09/2017	30	45	RECOURSE	RECOURSE
Sheet4

Cell Formulas
Range	Formula
N2	=IFERROR(IF(OR(E2>0.5*SUM(F2:J2),M2>60),"RECOURSE"),0)
O2	=IF(OR(E2>0.5*SUM(F2:J2),M2>60),"RECOURSE",0)