Automatically calculate amount of boxes (Efficiently)

Early Bright

New Member
Joined
Jan 30, 2020
Messages
6
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi all,

Not a regular poster on this forum but I do look here from time to time. I am currently struggling with the right way to calculate the amount of different kind of boxes to pack products in.

For example

I have 3 kind of boxes;
Box A - 80 per box
Box B - 60 per box
Box C - 25 per box

When a customer orders 140 of this product, I managed to get the result 1x Box A and 1x Box B. (QUOTIENT function)
But when the amount is for example 150, I still get the same result because 10 is left over. I would like to find out a way to get the remaining 10 pieces in Box C (25).

Also, another problem I run into is, when the customer orders 65 I would like to have as a result 1x Box A because it is more efficient than 1x Box B + 1x Box C.

I hope someone can help me figure this out, it would be greatly appreciated! :)
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
38,034
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
But when the amount is for example 150, I still get the same result because 10 is left over. I would like to find out a way to get the remaining 10 pieces in Box C (25).
Why wouldn't just place all 150 in two Box A's?
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
2,470
Office Version
  1. 365
  2. 2010
One option using a helper column:

Book1
ABCDE
1BoxQtyCalcAmountBoxes
2A8080150A 80, B 60, C 10
3B6060
4C2510
Sheet1
Cell Formulas
RangeFormula
E2E2=IF(D2<=C2,"A "&C2,IF(SUM(C2:C3)>=D2,"A "&C2&", B "&C3,"A "&C2&", B "&C3&", C "&C4))
C2C2=MIN(B2,D2)
C3C3=MIN(B3,D$2-C2)
C4C4=$D$2-SUM(C2:C3)
 

Early Bright

New Member
Joined
Jan 30, 2020
Messages
6
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
One option using a helper column:

Book1
ABCDE
1BoxQtyCalcAmountBoxes
2A8080150A 80, B 60, C 10
3B6060
4C2510
Sheet1
Cell Formulas
RangeFormula
E2E2=IF(D2<=C2,"A "&C2,IF(SUM(C2:C3)>=D2,"A "&C2&", B "&C3,"A "&C2&", B "&C3&", C "&C4))
C2C2=MIN(B2,D2)
C3C3=MIN(B3,D$2-C2)
C4C4=$D$2-SUM(C2:C3)
Thanks for your help! I will try this, I have to convert it to a Dutch formula. It is a little different I believe.
 

Forum statistics

Threads
1,175,955
Messages
5,900,528
Members
434,835
Latest member
cmenconi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top