Complex Formula Question - Conditional Sums

[GeordieKiwi]

Hi guys

I have a formula question I’ve been working on for a few days now and can’t seem to get my head round it, was hoping for some help.

I basically have 4 customers, and we have invoices/credits due in 6 time buckets 1D though to 90+D. If at a total level I owe the customer money I pay the appropriate amount assigned to that time bucket, however if the customer owes me money at a total level they will pay me that as soon as they owe me it at a total level. So both the customer and I pay on settlement date, unless at a net level the customer owes me money, then they will pay in advance.

i.e. For Customer C at a total they owe me 4m so will pay that on Day 1.

It then gets a more complex as we run through the buckets, for example Customer B in the 10 Day bucket I owe them 44m, but at that point at a total level they now owe me 18m so the net result is a 25m negative.

Essentially there are 4 scenarios:

1. The Remaining Total is always greater than 0 i.e. the customer owes me money (pays it all on Day 1)
2. The remaining Total is always less than 0 i.e. I owe the customer money (I pay in the appropriate time bucket)
3. The remaining Total moves from less than 0 to greater than 0 i.e. I owed the customer money and now the customer owes me money (I pay in the appropriate time bucket, the customer pays me the money in the time bucket as soon as the remaining total is greater than 0)
4. The remaining Total moves from greater than 0 to less than 0 i.e. the customer owed me money (pays it on Day 1) and then I owe the customer money (and pay it in the appropriate time bucket)

I hope that makes sense, really appreciate the input and thoughts guys,

Money to be paid/received based on settlement date
Row Labels	1D	2D	10D	30D	90D	90+D	Grand Total
Customer A	-	-	-	62,207.65	(19,557,160.16)	(21,369,901.82)	(40,864,854.33)
Customer B	324,911.11	-	(44,640,210.80)	-	18,738,457.69	-	(25,576,842.00)
Customer C	11,220.44	-	(7,658,005.75)	-	11,848,935.49	221,205.19	4,423,355.37
Customer D	-	(453,096.00)	35,740,680.10	2,230,689.30	-	(3,621,924.15)	33,896,349.25
Desired Result
Row Labels	1D	2D	10D	30D	90D	90+D	Grand Total
Customer A	-	-	-	62,207.65	(19,557,160.16)	(21,369,901.82)	(40,864,854.33)
Customer B	324,911.11	-	(25,901,753.11)	-	-	-	(25,576,842.00)
Customer C	4,423,355.37	-	-	-	-	-	4,423,355.37
Customer D	33,896,349.25	-	1,391,234.85	2,230,689.30	-	(3,621,924.15)	33,896,349.25

<colgroup><col width="281" style="width: 211pt; mso-width-source: userset; mso-width-alt: 10276;"> <col width="114" style="width: 86pt; mso-width-source: userset; mso-width-alt: 4169;"> <col width="105" style="width: 79pt; mso-width-source: userset; mso-width-alt: 3840;" span="5"> <col width="112" style="width: 84pt; mso-width-source: userset; mso-width-alt: 4096;"> <tbody> </tbody>

 

[GeordieKiwi]

Sorry this is how Customer D should read

Customer D

33,896,349.25

(453,096.00)

1,844,330.85

2,230,689.30

-

(3,621,924.15)

33,896,349.25

<colgroup><col width="281" style="width: 211pt; mso-width-source: userset; mso-width-alt: 10276;"> <col width="114" style="width: 86pt; mso-width-source: userset; mso-width-alt: 4169;"> <col width="105" style="width: 79pt; mso-width-source: userset; mso-width-alt: 3840;" span="5"> <col width="112" style="width: 84pt; mso-width-source: userset; mso-width-alt: 4096;"> <tbody> </tbody>