How to Optimise Factory Production Efficiency

[Fugdkn]

Hey guys. So below I have attached an example of a situation that I will regularly be posed with.

The minimum amount is 1008, and from there it goes up in multiples of 72. The goal is to ensure that different products (each row is a different product) are produced in batches that are equal amounts. So you want to try and make as many of them equal to each other, without just bumping up the amount you are producing by too much. An easy solution would just be to round everything up to the product with the highest production, but that would of course lead to way too much excess. So the goal is to try and make sure we batch things up (to the greatest extent possible) whilst creating minimal excess.

Also, as another rule, I would prefer nothing be rounded down at any point (only ever up as cutting production will lead to other issues in regard to not having enough of a product; the amounts written for each product are the bare minimums).

My current solution is just to round everything up to the nearest multiple of 2, which does work, but I am wondering if there is anyway I can improve this? Thanks.

[Fugdkn]

I actually managed to optimise it further by using offset. I think I may have solved my own problem, but if anyone has any ideas on how to improve this further, please do tell me.

[AliGW]

All in one:

=OFFSET(B3,COUNTIF(B4:B$29,"<"&CEILING(B3,1008)),0,1,1)

[Fugdkn]

Thanks a bunch!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.