how to get different cell groups each add up to a certain number

Question

I have an excel file that has one column which contains numbers that represent file sizes, each of those numbers is a floating point number with a max decimal places of two (Ex: 23.04). the total cells count is (277 cells).i want to automatically group cells that add up to (1000) or as close as possible to that number (1000). the sum of all the cells is (5739.49) so, there should be (4) groups of cells, each adds up to (1000) and the last one (5th group) doesn't matter how much it adds up to. now :

• I know that this can be solved using "THE SOLVER" in excel for each group But i'm not sure what constraints i should use and which solving method (i.e, the simplex, GRG nonlinear or the evolutionary) methods because i'm not familiar with it

thank You in advance

Answer 1

As I mentioned in the comment, the standard solver in Excel has a maximum limit for decision variables of 200.

This example is limited to 200 variables - if you have the premium solver, you can expand the solution. This was done using Excel 2016.

Worksheet setup

1. Column A contains the size data.
2. Column B contains the "Group" that each member in Column A will belong to. It is initially populated using the formula =RANDBETWEEN(1,5). It was then overridden using Copy / Paste Special - Values.
3. Column D contains the aggregated groups, labelled 1 through 5.
4. Column E contains the aggregated size within each group. It is calculated with =SUMIF($B$2:$B$201,"="&D2,$A$2:$A$201)
5. Column F contains the target value for each group (1000 for groups 1 through 4, ignored for group 5).
6. Column G contains the squared error from the target value for each group. It is calculated with =(E2-F2)^2
7. Cell G7 will be the "Set Objective" cell and is the sum of error squared. It is calculated with =SUM(G2:G5)

Below is a screen shot of the setup worksheet, before using solver.

Solver Setup

Solver is setup with the following criteria ...

1. "Set Objective:" is $G$7
2. "To:" is "Min"
3. "By Changing Variable Cells:" is $B$2:$B$201
4. "Subject to the Constraints:" includes: $B$2:$B$201 <= 5, $B$2:$B$201 = integer, and $B$2:$B$201 >= 1
5. "Select a Solving Method:" is Evolutionary. Note: You could use GRG, but it will be much slower.
6. In "Options", on the "All Methods" tab, in the "Solving with Integer Constraints" area, make sure Ignore Integer Constraints is not checked.

Below is a screen shot of the Solver Parameters dialog:

Solution

Your optimal region may be "flat" so there are multiple possible solutions.

Below is a screen shot of a solution I generated ...