Apply a max constraint and roll over the difference

[ILAW]

I am looking to write a formula for an ‘actual’ (Column C) that is equal to the ‘potential’ (Column A) but not exceed the ‘capacity’ (Column B).

In case the potential is greater than the capacity the excess (potential-capacity) is carried over into in next row until the capacity becomes available and fill the gap at that point. Ultimately, the entire of potential will be realized as actual over time as capacity becomes available.


ColumnA ColumnB ColumnC
(Potential) (Capacity) (Actual)
40 50 40
60 50 50
70 80 80 (actual now includes excess from the row above)
100 80 80
100 90 90
90 100 100 (actual includes some of the excess within the capacity constraint)
80 100 90 (actual includes remainder of the excess as capacity is now available)

[alansidman]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[harrisonland]

EDIT: Ooops. Too late!

Hi there and welcome to the forum. Just a couple of things...

• One of the mods is going to come along and point out that your thread subject doesn't really say what the question is about. They might change it for you since you're new around here on this occasion, but it might be good to get ahead of the game and change it before they get here.
  
• To be able to help you, please can you attach a sample worksheet, so we have something to work on. You're less likely to get some help if anyone wanting to help out has to start by putting your data in a worksheet to begin with.

Please see the yellow banner at the top of the page for details, so we can give you a hand.

Tim

[ILAW]

thanks for the reminder on the rules. i have fixed the title and hopefully that is clear on what i am trying to do

[ILAW]

I am attaching a simple spreadsheet with column C on this showing the results I want and Column D with some description on what I want. Thanks for your help

[MarvinP]

Hi ILaw and welcome to the forum,
I think something like:

Please Login or Register  to view this content.

is what you seek. See it in the attached. I think you got row 4 wrong.
Capacity Constraint Answer.xlsx
The red "$" in the above formula is the trick to keeping track of how much is extra as you move down the rows.
I think one of my attached files doesn't have the correct "$" so look at both.

[ILAW]

Hi Marvin. Thanks for the response. Your formula seems to work except in row4.

I had row4 correct as the potential exceeds the capacity constraint in row3 and the difference rolls over to row4.

[harrisonland]

I've been pondering this one, too. I don't think he has got it wrong on row 4.

I was going to do precisely what you did, but it occurred to me that you can't just subtract the column B sums from those in column A.

If "potential">"capacity", the "bank" should increase, but if "capacity"<"potential" it remains unchanged. Your formula would actually deduct from the "bank" in the second scenario, which I don't think it should.

Was thinking about a helper column, but I'm too tired to give it any more thought tonight.

Off to bed, said Zebedee.
Tim

[ILAW]

Tim,

Yes, you are right row 4 was not wrong. Actually my row 8 and Marvin's calculated row 8 were both wrong.

I am attaching the spreadsheet again with my Column C now showing the answers I need.

Column D is Marvin's formula that seems to breakdown in row 4 and 8.

A simple check for the correct calculation would be that it should sum upto Column A like mine does in Column C

Sleep well...

[harrisonland]

Argh!!! I wish I didn't get so bogged down with what I find to be puzzling! I was supposedly going to bed 1/2hr ago, but my curiosity got the better of me - my wife's going to kill me (again).

Anyway, I've used a couple of helper cells -

• one to calculate any potential to be banked (per line)
• another to keep a running bank total by reducing the "bank" by any potential used, and increasing it where possible

I *think* this is right now, although not pretty (I'm sure MarvinP will be able to beautify it) - and I'm open to correction.

HTH
Tim

[MarvinP]

Hey guys,

You are putting me on the spot, thinking I'll come up with a cute solution. I think the question is poorly worded. Can the potential roll down more than one row or as many rows as needed? In normal questions there is only one question mark and you get to find the number of miles or pounds or ?? to figure it out. This question is different as there is no physical question mark in the first thread. Can we only use the potential from the row directly above our current working row or the sum of the potentials accumulated in the above system (all rows above, if any)?

It actually reminds me of those statistics problems where you pull black and/or white balls out of a bag and predict the probability of pulling a set combination of balls. The questions frequently didn't mention if the balls stayed out of the bag or if you got to put them back in before the next pull. In these cases you'd look in the back of the teachers book for the correct answer or look at the main topic of the chapter to figure that part out.

I think this problem as two dams called Potential which is upstream from the other called Capacity. Water flows through Potential every day (row) and Capacity sends it downstream. How much water goes downstream each day? (note the question mark here). The first day 40 went through the top dam and Capacity let it all run downstream. That matches the answer we were given. The second day Potential had to hold onto 10 as only 50 was allowed downstream. On day 3 (row 4) the problem needs improvement. Can we use the water left over from the previous day only, or all past days, or can we only pass the amount that came through previous that day.

In other words the problem is poorly defined. The rules changed. We put the balls back in the bag some times and not others to do the problem. Sorry - no cute answer without a better answer in the back of the book.

Not to fret as lots of questions have no answers (or question marks in them ).

[Phuocam]

Maybe?

=MIN(B3,SUM($A$2:A3)-SUM($C$2:C2))

[ILAW]

Tim, This works. It gives the answers I was expecting. Staying up for this was worthwhile afterall:-). Thanks

[ILAW]

Yes. This works, too. and without any helper cells! Thanks

[harrisonland]

Maybe?

=MIN(B3,SUM($A$2:A3)-SUM($C$2:C2))

Amazing what a fresh pair of eyes can do!

Tim

[harrisonland]

If you consider this sorted, please don't forget to mark it "Solved" using the thread tools at the top of the page.

You might also be so kind as to use the "*" button if you think answers you got were helpful!

Tim

[ILAW]

Maybe?

=MIN(B3,SUM($A$2:A3)-SUM($C$2:C2))

Excellent job!