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]

this is the exact formula as in post #4

Excel 2013/2016
	A	E	F	G	H	I	J	K	L	M	N
1	Client	Balance	Current	P1	P2	P3	Older	Due Date	Payment Terms	Days Overdue	Debt Status
2	Customer 6	6,100.00			4100			08/09/2017	30	45	RECOURSE
3	Customer 6	6,100.00			1000			08/09/2017	30	45	0
4	Customer 6	6,100.00			1000			08/09/2017	30	90	RECOURSE
Sheet4

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

 

[kevzmm]

this still isnt quite right.

So ill explain the situation a bit;

Customer 1 has 3 invoices over 60 days so they are marked as recourse.

if the total recourse amount is higher than 50% of all the total value of all the invoices then every invoice for that customer should be marked as recourse.

 

[AlanY]

ok, try this

Excel 2013/2016
	A	E	F	G	H	I	J	K	L	M	N
1	Client	Balance	Current	P1	P2	P3	Older	Due Date	Payment Terms	Days Overdue	Debt Status
2	Customer 6	6,100.00			3500			08/09/2017	30	65	RECOURSE
3	Customer 6	6,100.00			1000			08/09/2017	30	45	RECOURSE
4	Customer 6	6,100.00			5000			08/09/2017	30	45	RECOURSE
Sheet4

Cell Formulas
Range	Formula
N2	=IF(OR(SUM($F$2:$J$4)>0.5*SUM($E$2:$E$4),M2>60),"RECOURSE",0)

 

[kevzmm]

Many thanks im sure this will work, but is there any way of doing this with out doing a sum f2:J4 as this data contains many customers and many invoices which are updated daily, which would the formula would have to be updated each time.

 

[AlanY]

yes, but i'll log out in a couple of minutes.
will have a look tomorrow if you haven't figured it out then

 

[kevzmm]

Many thanks.

I have thought this might be a vlookup or a goal seeking function.

so far with every formula i have used that you've given, everything says recourse even if it isnt.

This is a fairly complicated situation i appreciate that and if i can get this working it would be the best thing ever. I have thought of Pivoting the data but it is linked to another pivot so thats not an option.

 

[kevzmm]

What i was also thinking if this makes it slightly easier is to add another column next to balance which shows the invoice balance, the total of invoices for each customer.
again this would have to be a look up of some sort or some and cant just be a sum A1:A4 because the data is ever changing.

Thanks.

 

[AlanY]

try this, extend ranges to suit

Excel 2013/2016
	A	E	F	G	H	I	J	K	L	M	N
1	Client	Balance	Current	P1	P2	P3	Older	Due Date	Payment Terms	Days Overdue	Debt Status
2	Customer 6	6,100.00			1000			08/09/2017	30	65	RECOURSE
3	Customer 6	6,100.00			5000			08/09/2017	30	45	RECOURSE
4	Customer 1	6,100.00			1000			08/09/2017	30	45	0
5	Customer 6	6,100.00			5000			08/09/2017	30	45	RECOURSE
Sheet4

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

 

[kevzmm]

hi i tried this and extended ranges manipulated the formula to suit and everything just came out as recourse even if it wasnt

 

[AlanY]

would you post a sample of your data that we can test it out