Sum Consecutive negative numbers with condition

[CTF]

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

 

[6StringJazzer]

The way to deal with this is to have a running deficit column.

I am also surprised the Charge column is not a formula.

If your contract amount is constant then you should move it to a single cell instead of repeating it in a column.

See attached.

 

[Peter Bartholomew]

I tried a 365 solution but it took a while to identify the structure of the problem. It is essentially a accumulation problem in which an outflow is only permitted if the balance is positive. This requires a constrained accumulation followed by differencing.

= LET(
      balance, Accumulateλ(Count-contracted),
      flow, Diffλ(balance),
      IF(Count>contracted, Count+ flow, contracted)
  )

where Accumulateλ and Diffλ are defined to be

= LAMBDA(del, SCAN(0,del, LAMBDA(acc,d, IF(d<acc, acc-d, 0))))

= LAMBDA(accum, accum - INDEX(accum,SEQUENCE(COUNT(accum),,0)))

respectively.

 

[CTF]

The way to deal with this is to have a running deficit column.

I am also surprised the Charge column is not a formula.

If your contract amount is constant then you should move it to a single cell instead of repeating it in a column.

See attached.

Thanks 6StringJazzer, and well done on solving my problem. I guess I was trying too hard to put it all in one column. FYI, the Charge column is a formula and the actual worksheet I use does reference just one cell, but I was making sure I presented the problem as clear as possible.

Thanks again

 

[CTF]

I tried a 365 solution but it took a while to identify the structure of the problem. It is essentially a accumulation problem in which an outflow is only permitted if the balance is positive. This requires a constrained accumulation followed by differencing.

= LET(
      balance, Accumulateλ(Count-contracted),
      flow, Diffλ(balance),
      IF(Count>contracted, Count+ flow, contracted)
  )

where Accumulateλ and Diffλ are defined to be

= LAMBDA(del, SCAN(0,del, LAMBDA(acc,d, IF(d<acc, acc-d, 0))))

= LAMBDA(accum, accum - INDEX(accum,SEQUENCE(COUNT(accum),,0)))

respectively.

Thanks Peter, unfortunately, my work does not have Office 365 yet (only 2019), but I do have 365 at home so I will try this one home. I have seen some YouTube videos on Lambda, so I think I grasp your solution. Well done and thanks for taking the time to solve my problem.

Thanks


Craig

 

[bosco_yip]

Another formula option for all non Office 365 users

1] Create a name range as in :

Select D5 >>

• Name : MonthsCharge
• Refers to : =INDEX($C$4:$C4,MATCH(1,0/($D$4:$D4<>$C$2))+1):$C4

2] Then, in D5 formula copied down :

=MAX(C5+IFERROR(SUM(MonthsCharge)-C$2*COUNT(MonthsCharge),0),C$2)

 

[Peter Bartholomew]

Hi @bosco_yip, somebody needs to get you a 365 license! With your level of skill in developing formulas, it would be nice seeing what you could do shaping the future rather than supporting the past!

 

[bosco_yip]

Hi @bosco_yip, somebody needs to get you a 365 license! With your level of skill in developing formulas, it would be nice seeing what you could do shaping the future rather than supporting the past!

Hi Peter,

Thank you for your support.

 

[6StringJazzer]

I tried a 365 solution

This is a brilliant solution using the power of new functions to shortcut directly to the Charge column.

I would be interested in knowing more about the OP's business requirements. If it were me I would want to see the audit trail of shortfalls on each order and how carryovers were applied for each order, rather than removing those columns.

 

[Peter Bartholomew]

I would be interested in knowing more about the OP's business requirements. If it were me I would want to see the audit trail of shortfalls on each order and how carryovers were applied for each order, rather than removing those columns.

There is probably a balance to be struck. Too much in the way of intermediate 'workings' and you simply create 'sheet junk' that distracts from, rather than adds to, understanding. One of the (worst) financial modelling standards had as a requirement that the user should be able to check the calculation from printed copy using a pocket calculator! I would argue that this is misdirected effort. Excel does not get the sums wrong; it is the user that enters the wrong formula and hence it is the formula that should be checked.

I tend to work at the level of 'output requested within the user-requirements should be provided' and 'a competent practitioner should be capable of creating code to move from one step to the next'. Otherwise, it is the case that 'less is more'.