Complex Formula Question - Conditional Sums

G

GeordieKiwi

New Member
Joined
Jan 8, 2018
Messages
2
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,

[TABLE="width: 776"]
<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>[TR]
[TD="class: xl65, width: 500, bgcolor: transparent, colspan: 3"] Money to be paid/received based on settlement date [/TD]
[TD="class: xl65, width: 105, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 105, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 105, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 105, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 112, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] Row Labels [/TD]
[TD="class: xl66, bgcolor: transparent"] 1D [/TD]
[TD="class: xl66, bgcolor: transparent"] 2D [/TD]
[TD="class: xl66, bgcolor: transparent"] 10D [/TD]
[TD="class: xl66, bgcolor: transparent"] 30D [/TD]
[TD="class: xl66, bgcolor: transparent"] 90D [/TD]
[TD="class: xl66, bgcolor: transparent"] 90+D [/TD]
[TD="class: xl66, bgcolor: transparent"] Grand Total [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] Customer A [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] 62,207.65 [/TD]
[TD="class: xl66, bgcolor: transparent"] (19,557,160.16)[/TD]
[TD="class: xl66, bgcolor: transparent"] (21,369,901.82)[/TD]
[TD="class: xl66, bgcolor: transparent"] (40,864,854.33)[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] Customer B [/TD]
[TD="class: xl66, bgcolor: transparent"] 324,911.11 [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] (44,640,210.80)[/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] 18,738,457.69 [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] (25,576,842.00)[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] Customer C [/TD]
[TD="class: xl66, bgcolor: transparent"] 11,220.44 [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] (7,658,005.75)[/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] 11,848,935.49 [/TD]
[TD="class: xl66, bgcolor: transparent"] 221,205.19 [/TD]
[TD="class: xl66, bgcolor: transparent"] 4,423,355.37 [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] Customer D [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] (453,096.00)[/TD]
[TD="class: xl66, bgcolor: transparent"] 35,740,680.10 [/TD]
[TD="class: xl66, bgcolor: transparent"] 2,230,689.30 [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] (3,621,924.15)[/TD]
[TD="class: xl66, bgcolor: transparent"] 33,896,349.25 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] Desired Result [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] Row Labels [/TD]
[TD="class: xl66, bgcolor: transparent"] 1D [/TD]
[TD="class: xl66, bgcolor: transparent"] 2D [/TD]
[TD="class: xl66, bgcolor: transparent"] 10D [/TD]
[TD="class: xl66, bgcolor: transparent"] 30D [/TD]
[TD="class: xl66, bgcolor: transparent"] 90D [/TD]
[TD="class: xl66, bgcolor: transparent"] 90+D [/TD]
[TD="class: xl66, bgcolor: transparent"] Grand Total [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] Customer A [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] 62,207.65 [/TD]
[TD="class: xl66, bgcolor: transparent"] (19,557,160.16)[/TD]
[TD="class: xl66, bgcolor: transparent"] (21,369,901.82)[/TD]
[TD="class: xl66, bgcolor: transparent"] (40,864,854.33)[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] Customer B [/TD]
[TD="class: xl66, bgcolor: transparent"] 324,911.11 [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] (25,901,753.11)[/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] (25,576,842.00)[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] Customer C [/TD]
[TD="class: xl66, bgcolor: transparent"] 4,423,355.37 [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] 4,423,355.37 [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] Customer D [/TD]
[TD="class: xl66, bgcolor: transparent"] 33,896,349.25 [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] 1,391,234.85 [/TD]
[TD="class: xl66, bgcolor: transparent"] 2,230,689.30 [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] (3,621,924.15)[/TD]
[TD="class: xl66, bgcolor: transparent"] 33,896,349.25 [/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
Sorry this is how Customer D should read

[TABLE="width: 776"]
<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>[TR]
[TD="class: xl65, width: 281, bgcolor: transparent"] Customer D [/TD]
[TD="class: xl65, width: 114, bgcolor: transparent"] 33,896,349.25 [/TD]
[TD="class: xl65, width: 105, bgcolor: transparent"] (453,096.00)[/TD]
[TD="class: xl65, width: 105, bgcolor: transparent"] 1,844,330.85 [/TD]
[TD="class: xl65, width: 105, bgcolor: transparent"] 2,230,689.30 [/TD]
[TD="class: xl65, width: 105, bgcolor: transparent"] - [/TD]
[TD="class: xl65, width: 105, bgcolor: transparent"] (3,621,924.15)[/TD]
[TD="class: xl65, width: 112, bgcolor: transparent"] 33,896,349.25 [/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Another Sum If By Color Question
Replies
0
Views
832
McSkillet
M
N
  • Question Question
Need help creating an aging measure
Replies
1
Views
961
Ozeroth
O
M
  • Question Question
Date with Multiple IF Condition Formula
Replies
3
Views
533
abi_learn_vba
A
A
  • Question Question
Complex formula
Replies
6
Views
735
tpease
T
B
  • Question Question
Hide Error Value & Keep Cell Blank
Replies
8
Views
8K
GaryWMn
GaryWMn

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top