Hi, this is my first post; normally I have been able to resolve my problems by scouring this website and finding the answer I need.
I receive monthly customer machines counts, which are shown in column C2. The customer is on a minimum contract, so the lowest monthly count we can charge them is 11,000 copies (D2). When they exceed this minimum count they are charged the total count (11,000 + anything above). If they are under the minimum count, then they are charged 11,000, but the shortfall can be used to offset any months where they exceed their contract amount in the future. The first example of this is seen in cell F5. The customer exceeded the 11,000 minimum count by 13,439 copies so they should have been charged 24,439 copies, but the previous month they were 213 copies under the contract amount, so they were instead changed for 24,226 (24,439-213=24,226).
Where it gets difficult to make any formula work is having a big deficit amount that can span for more than one month. See F8 as a good example. The customer again exceeded the 11,000 contract amount and used 14,132, but was only changed 13,522 because they were able to draw on a previous a large deficit two months prior. I hope this is making sense. I have made additional comments in the attached spreadsheet.
Thanks in advance if this can be solved.
Craig
I receive monthly customer machines counts, which are shown in column C2. The customer is on a minimum contract, so the lowest monthly count we can charge them is 11,000 copies (D2). When they exceed this minimum count they are charged the total count (11,000 + anything above). If they are under the minimum count, then they are charged 11,000, but the shortfall can be used to offset any months where they exceed their contract amount in the future. The first example of this is seen in cell F5. The customer exceeded the 11,000 minimum count by 13,439 copies so they should have been charged 24,439 copies, but the previous month they were 213 copies under the contract amount, so they were instead changed for 24,226 (24,439-213=24,226).
Where it gets difficult to make any formula work is having a big deficit amount that can span for more than one month. See F8 as a good example. The customer again exceeded the 11,000 contract amount and used 14,132, but was only changed 13,522 because they were able to draw on a previous a large deficit two months prior. I hope this is making sense. I have made additional comments in the attached spreadsheet.
Thanks in advance if this can be solved.
Craig