Excel formula required to calculate debtor days using the count back method

[RD1567]

Hi

I am trying to create a spreadsheet to calculate debtor days using the count back method - but cant seem to get the correct answer. please see formula being used currently and data i am trying to work with:


The formula i am using for the days is shown at the bottom - but is obviously not correct!

Essentially in some cases i may need to count back 3 mths and in some cases up to 4 mths so the formula needs to account for any number of months.

Can anyone help?!

Best regards

P Q R S T U V W X Y Z AA AB AC AD AE AF Jan'18 Feb'18 Mar'18 Apr'18 May'18 Jun'18 Jul'18 Aug'18 Sep'18 Oct'18 Nov'18 Dec'18 Jan'19 Feb'19 Mar'19 Apr'19 Days in Month 31.00 28 31 30 31 30 31 31 30 31 30 31 31 28 31 30 4 Fees billed in Month 5 Company 3,000,000.00 2,000,000.00 2,500,000.00 4,200,000.00 6 Dept 1 300,000.00 100,000.00 150,000.00 300,000.00 7 Dept 2 300,000.00 250,000.00 300,000.00 300,000.00 8 Dept 3 185,000.00 150,000.00 190,000.00 200,000.00 9 Dept 4 420,000.00 450,000.00 500,000.00 1,300,000.00 10 Dept 5 420,000.00 165,000.00 233,000.00 620,000.00 11 Dept 6 150,000.00 100,000.00 50,000.00 120,000.00 12 Dept 7 150,000.00 100,000.00 120,000.00 86,000.00 13 Dept 8 150,000.00 200,000.00 180,000.00 750,000.00 14 Dept 9 150,000.00 100,000.00 130,000.00 100,000.00 15 Dept 10 300,000.00 200,000.00 180,000.00 100,000.00 16 Dept 11 300,000.00 200,000.00 275,000.00 350,000.00 17 Dept 12 30,000.00 30,000.00 30,000.00 45,000.00 18 Dept 13 - - - 500.00 19 £ 2,855,000.00 £ 2,045,000.00 £ 2,338,000.00 £ 4,271,500.00 £ - £ - £ - £ - £ - £ - £ - £ - £ - £ - £ - £ - 20 21 Debtors at the end of current month 22 23 Company 8,400,000.00 24 Dept 1 500,000.00 25 Dept 2 400,000.00 26 Dept 3 500,000.00 27 Dept 4 2,000,000.00 28 Dept 5 1,600,000.00 29 Dept 6 400,000.00 30 Dept 7 300,000.00 31 Dept 8 900,000.00 32 Dept 9 400,000.00 33 Dept 10 300,000.00 34 Dept 11 1,000,000.00 35 Dept 12 100,000.00 36 Dept 13 - 37 £ - £ - £ - £ 8,400,000.00 £ - £ - £ - £ - £ - £ - £ - £ - £ - £ - £ - £ - 38 39 Debtor Days - countback 40 41 Company 96 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 42 Dept 1 80 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 43 Dept 2 42 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 44 Dept 3 86 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 45 Dept 4 105 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 46 Dept 5 120 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 47 Dept 6 115 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 48 Dept 7 72 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 49 Dept 8 105 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 50 Dept 9 96 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 51 Dept 10 31 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 52 Dept 11 109 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 53 Dept 12 97 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 54 Dept 13 0 0 0 0 0 0 0 0 0 0 0 0 0 Formula (MIN(T23,Q5)/Q5*Q$3) PLUS (MIN(T23-Q5,R5)/R5*(T23>=Q5)*R$3) PLUS (MIN(T23-SUM(Q5:R5),S5)/S5*(T23>=SUM(Q5:R5))*S$3) PLUS (MIN(T23-SUM(Q5:S5),T5)/T5*(T23>=SUM(Q5:S5))*T$3) <colgroup><col><col><col><col><col><col><col><col span="11"></colgroup><tbody> </tbody>

<tbody>
</tbody>