Sum cannot be more than Cell Value, Capacity

[swiftrain]

This sheet shows I have a quantity I need to run (Col D), but I have an overall capacity number I cannot go over (A2). Each Product has to be run by their lot size and I cannot roundup to the next lot size if it means I go greater than A2. C10 is what will be compared to the "A2" number.


cap #.xlsx

[Ron Coderre]

I'm not 100% sure of the requirements, but I think this gets you at least part of what you want...

Using your posted workbook:

This regular formula begins the list of full Lot_Sizes that can be run without going over the Qty_Needed

Please Login or Register  to view this content.

Copy that formula down through C9.

With the sample data, these are the results:

Please Login or Register  to view this content.

Is that something you can work with?

[swiftrain]

The idea is to be able to make the quantities according to their lot sizes, (i.e., if prod 1 is a lot size of 20 then it either needs to produce by multiples of 20 or nothing at all). Not sure if that helps.

[Ron Coderre]

Nope...doesn't help

The formula I posted would produce whole lots of Prod 1 until the next lot would exceed the Qty Needed.
If the Qty need for Prod 1 is: 210
The formula would still indicate an Able_to_Run value of 10...because running an 11th lot would create 220 (larger than the Qty_Needed)

If that's not what you want, some complete examples would be helpful.

[swiftrain]

I don't know if this will help. I added a results column to show what I could run under the current capacity or (bottleneck) in production. It shows a total that is less than A2 and also shows the multiples of the lot sizes that can be run to meet that number.

cap #.xlsx

[Ron Coderre]

OK...now I'm really confused...and obviously missing something.

If you simply wanted to produce the maximum full lots up to A2 (200), why not just make 10 lots of Prod 1 (which would total 200) and be done?

[swiftrain]

I need to make a balance out of each item. I was thinking of adding a % column to distribute the product, but I have to be able to run as many different products as possible. Believe me, I would love to run just one product at a time.