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]

ok, think we might finally cracked it

Excel 2013/2016
	A	B	E	F	G	H	I	J	L	M	N	O
1	Client	Date	Balance	Current	P1	P2	P3	Older	Due Date	Payment Terms	Days Overdue	Debt Status
2	Customer 1	25/10/2017	£480.00	£480.00					24/11/2017	30	-15	FALSE
3	Customer 1	17/10/2017	£2,400.00	£2,400.00					16/11/2017	30	-7	FALSE
4	Customer 1	17/10/2017	£480.00	£480.00					16/11/2017	30	-7	FALSE
5	Customer 2	30/04/2017	£1,980.00					£1,980.00	30/05/2017	30	163	RECOURSE
6	Customer 2	24/05/2017	£1,980.00					£1,980.00	23/06/2017	30	139	RECOURSE
7	Customer 2	10/05/2017	£1,584.00					£1,584.00	09/06/2017	30	153	RECOURSE
8	Customer 2	09/08/2017	£1,980.00			£1,980.00			08/09/2017	30	62	RECOURSE
9	Customer 3	24/10/2017	£46,800.00	£46,800.00					23/11/2017	30	-14	FALSE
10	Customer 4	16/11/2016	£2,376.00					£2,376.00	16/12/2016	30	328	RECOURSE
11	Customer 5	31/08/2017	£11,280.00		£11,280.00				30/09/2017	30	40	FALSE
12	Customer 6	17/10/2017	£8,400.00	£8,400.00					16/11/2017	30	-7	FALSE
13	Customer 7	30/09/2017	£9,828.00	£9,828.00					30/10/2017	30	10	FALSE
Sheet4

Cell Formulas
Range	Formula
O2	=IF(OR(SUMPRODUCT(($A$2:$A$100=A2)*$F$2:$J$100)<0.5*SUMPRODUCT(($A$2:$A$100=A2)*$E$2:$E$100),N2>60),"RECOURSE")

 

[kevzmm]

im really sorry i couldnt get this working. this one is very frustrating. i just hope we can find a solution

im just getting Value!

 

[AlanY]

have a look of this,

https://drive.google.com/file/d/1lLK8lKiOPL5aX1xLSBbtMAuWarFNlfeP/view?usp=sharing

and let us know how you getting on

 

[kevzmm]

hi i got the formula looking like it works in my master file but looked at one customer i know should be in recourse but it doesnt show it is

 

[AlanY]

you can paste the problem data here, but unfortunately i'll off in a minute and I'll have a look tomorrow