Welcome to the Board!

This is a variation of the Knapsack Problem. You can Google that, or check Wikipedia, but the upshot is that it's really a tricky problem. There are algorithms to do this, but it'd take more time than I have to adapt one of them to your particular problem. Excel has a tool called Solver that might work for you however. It's not installed by default though. If you don't have it on your Data tab, click File > Options > Add-ins > Go (on the bottom next to Excel Add-ins) > check the Solver box.

Now set up a new spreadsheet and set it up like this:

| A | B | C | D | E | F | G | H | I | J |
---|

1 | Order | Volume | | Shipment | | 1 | 2 | 3 | 4 | |

2 | 1 | 145 | | | | 0 | 0 | 0 | 0 | |

3 | 2 | 135 | | | | | | | | |

4 | 3 | 846 | | | | Std. Dev. | | | | |

5 | 4 | 837 | | | | 0 | | | | |

6 | 5 | 349 | | | | | | | | |

7 | 6 | 161 | | | | | | | | |

8 | 7 | 549 | | | | | | | | |

9 | 8 | 1595 | | | | | | | | |

10 | 9 | 1595 | | | | | | | | |

11 | 10 | 1908 | | | | | | | | |

12 | 11 | 1895 | | | | | | | | |

13 | 12 | 1991 | | | | | | | | |

14 | | | | | | | | | | |

15 | | Max Per shipment | 3400 | | | | | | | |

16 | | Minimum Orders | 3.531176 | | | | | | | |

<tbody>

</tbody>

**Sheet2**

**Worksheet Formulas**
Cell | Formula |
---|
F2 | =SUMIF($D$2:$D$13,F1,$B$2:$B$13) |
---|
G2 | =SUMIF($D$2:$D$13,G1,$B$2:$B$13) |
---|
H2 | =SUMIF($D$2:$D$13,H1,$B$2:$B$13) |
---|
I2 | =SUMIF($D$2:$D$13,I1,$B$2:$B$13) |
---|
**F5:** =STDEV.P(F2:I2) | | C16 | =SUM(B2:B13)/C15 |
---|
<tbody>
</tbody> |

<tbody>

</tbody>

The formula in C16 will tell you the minimum number of shipments you'll need (rounded up). So in F1:I1 put the numbers 1 to 4. Then put the formula in F5.

Now from the Data tab, click Solver (far right). Set up the parameters like this:

Set Objective: F5

To: Min

By Changing Variable Cells: D2:D13

Subject to the Constraints:

D2:D13<=

4
D2:D13 = integer

D2:D13 >=1

F2:I2<=C15

Solving Method: Evolutionary

and click Solve. It'll ponder for a while (you can see the stats in the status bar) and hopefully come up with a solution. If it doesn't, and it may not, don't give up hope yet. The algorithm is very dependent on starting conditions. Change D2:D13 all to 4 and try again. The numbers in column D will be the shipment that the order on that line should go in. If you still don't get a good solution after a few tries, there may not be a solution, or this algorithm might not find it. Then you might have to assume that you need 5 shipments. You'd just add a 5 in J1, the formula in J2, and update the formula in F5. Then try Solver again, changing the constraints I marked in red.

Incidentally, I chose Std. Dev. as the formula since that will have the effect of balancing the shipment sizes as much as possible. Other options are possible.

Hope this works for you!