Round up formula to "fill a case"

[bballch28]

Hey guys, new to the forum. I have a small project I'm working on for work. We order different beverage cartons through a supplier but they charge us a partial case fee if we don't order full cases. We have two different size units. 8 units fills up a case. The two sizes are 1 or 2. I programmed a formula that calculates what I need to order on a weekly basis but it typically is a few units off of being a full case (i.e. 6 units needed OR 13 units needed, etc.) I have conditional formatting to let me know that it's in increments of 8.

My question is this:

Based on the rank of each beverage, is there a way to add beverages automatically (either 1 or 2 unit cartons) to get the final total in increments of 8? If the unit count is at 15 but a 2-unit beverage is the highest rank cartridge that qualifies to fill a case, I need to skip it and add the highest ranked 1-unit cartridge instead so that it's an even 16. Any reference or help with the formula is very much appreciated.

[danielexcelvba]

A few things:

1) I recommend you change the thread title to something that is descriptive of the title (otherwise a moderator will tell you to do it and lock the thread until you do)
2) Where does it say if the unit size is 1 or 2?
3) Where does it show your ranking?
4) Where is your formula to determine how many cases need to be ordered (is it in cell T28?)

[bballch28]

1) Thanks, I updated it.
2) I updated attachment and I've unhidden everything. "MM Lemonade" thru "Mello Yello" are 2-unit cartridges. Everthing else is 1.
3) UPDATED: K:K
4) "W29" which is the sum of "U28" & "W28". The "W" column has values I've added manually to get the value to something divisible by 8.

[danielexcelvba]

One more question: say you need 6 beverages (e.g. you are at 10). Would you want it to select the highest rank 3 times, or ranks 1,2 and 3?

[danielexcelvba]

Test Reply

[danielexcelvba]

Ok two possible solutions:
1) Using the solver add-on: row AI shows which drinks to select to fill up your order (1 = select, 0 = do not select). This will not choose multiple copies of the same drink, but it can be edited to do so.
Note: the formula I used could potentially be "tricked", if you have very low ratings for all your "Singles", and very high ratings for all your "Doubles", but looking at your data that seems quite unlikely. The columns used are AI-AK.
2) Using formulas: cells X33-Z37 show a formula solution. The cells in "X" show which drink (by ranking) you should order, the cells in "Y" show the number of drinks still required, and the cells in "Z" show if the drink is single or double.

Let me know if you have any questions, or if neither of these are what you're looking for.