Set Max Amount but Add Balance to Next Available Row - Production Forecasting

[tsbuff]

I am trying to set a maximum amount allowed in my "Adjusted Number Column" (D). But I also want to carry that excess amount to the next availalbe row.

See attached workbook.

For example, the max is set to 10.

Cell B4 is 12, and cell B5 is only 4, so the adjusted amount in cell D4 is "10" (the max), and the excess amount of "2" from B4 can be added to the adjusted amount cell D5, equalling "6".

Here is where it gets tricky:

Cell B6 is "11", and Cell B7 is "14", so both of their adjusted amounts, cells D6 and D7, will be "10".

But I don't want to lose the excess amounts, "1" from B6, and "4" from B7. Since, B8 is only 1, then its adjusted cell D8, can include both the "1" and "4", so it equals "6"

Still following me.

Furthermore, if all of an excess amount can't be added to the next available row, I still want whatever can be added to it to be included in the adjusted amount.

For example,

Cell B9 is "15", over by "5", so it's adjusted amount is "10".

But the next available row is not B10, but B11, which has a value of "6", so it can accept an additional "4" in its adjusted cell D11, which equals "10", now.

And finally, Cell B12 is only "1", so it can accept the remaining "1" and add it to its adjusted value cell D12, equals "2".

The purpose of this spreadsheet is to forecast production in an office environment. The "Amount" column represents how much business is received. The "Max" value represents the maximum amount of output. Therefore, I can't just cut off new business that has been received, it must be rolled over into the next available day.

Any suggestions are welcome and I hope I explained it well enough.

[FDibbins]

tsbuff, welcome to the forum

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[tsbuff]

I uploaded a dummy workbook as you suggested. Thank you.

[FDibbins]

in C2, copied down, try this,,,
=IF(B2<=Max,0,B2-Max)
in D2, dopied down, try this...
=IF(B2<=Max,B2+C1,IF(B2+C1>=Max,Max,B2+C1))

this should work unless you start getting an overflow across more than 1 row. eg
row4 =12
row5 =19

[tsbuff]

Yes, that does carry excess amounts from one day to the next but it does'nt meet all conditions, including carrying over excess amounts over multiple days. Is there a solution for this?

[tsbuff]

Anybody else got any ideas? Is this really that difficult?

[bebo021999]

Anybody else got any ideas? Is this really that difficult?

Not too difficult, but alittle headache.
First row:
D2=MIN(B2-C2,10)
D3 and drag down:
=MIN(B3-C3+SUM(B$2:B2)-SUM(D$2:D2),10)

[tsbuff]

Not too difficult, but alittle headache.
First row:
D2=MIN(B2-C2,10)
D3 and drag down:
=MIN(B3-C3+SUM(B$2:B2)-SUM(D$2:D2),10)

Excellent! Thank you Bebo. I actually figured out a different method on my own but ended up adding two more columns where a running total of excess amount was calculated, and a column that calculated what would be added to the adjusted amount. But your way is is much simpler. Thanks so much!