Grouping Numbers

[meighkee]

Greetings,

I need help with something I was tasked to do for work. I'm familiar enough with Excel to know this is possible, but not sure how to develop the formula. I have a list of items with prices that I want to group into 4 similar totals. For example, I may have 12 items (no particular order) that I want to divide up into 4 groups, each with a total cost somewhat close to the other groups. Excel will look at the list of cells with numbers, and change the order into 4 groups each with similar totals. I hope that makes sense.

Don't see an option to post an attachment, but have a simple example I can provide.

Any help greatly appreciated.

Thank You,

 

[Eric W]

You could use the Solver for something like this. If you don't have the Solver installed, click File > Options > Add-ins > Excel Add-ins (on the bottom) > Go > check the Solver box > OK.

Now set up your sheet like this:

Excel 2012

	A	B	C	D	E	F	G	H	I	J
1	Product	Price	Group		Group1	Group2	Group3	Group4		Difference
2	Product 1	11			0	0	0	0
3	Product 2	68			0	0	0	0		0
4	Product 3	89
5	Product 4	33
6	Product 5	72
7	Product 6	95
8	Product 7	14
9	Product 8	87
10	Product 9	53
11	Product 10	99
12	Product 11	79
13	Product 12	1
14	Product 13	12
15	Product 14	51
16	Product 15	50
17	Product 16	23
18	Product 17	7
19	Product 18	25
20	Product 19	10
21	Product 20	90

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula E2 =COUNTIF($C$2:$C$21,1) F2 =COUNTIF($C$2:$C$21,2) G2 =COUNTIF($C$2:$C$21,3) H2 =COUNTIF($C$2:$C$21,4) E3 =SUMIF($C$2:$C$21,1,$B$2:$B$21) F3 =SUMIF($C$2:$C$21,2,$B$2:$B$21) G3 =SUMIF($C$2:$C$21,3,$B$2:$B$21) H3 =SUMIF($C$2:$C$21,4,$B$2:$B$21) J3 =ABS(MAX(E3:H3)-MIN(E3:H3)) <tbody> </tbody>

<tbody>
</tbody>

Go to the Data tab, click Solver, and set up the problem like this:

Set Objective: J3
To: Min
By Changing Variable cells: C2:C21
Subject to the Constraints:
C2:C21<=4
C2:C21=integer
C2:C21 >=1
E2:H2=5
Solving Method: Evolutionary

Click Solve. The values in C2:C21 will be which group each product should go in. You can probably figure out what all the values are: 5 is the number of items in each group, 4 is the number of groups, etc. The Solver will go through thousands of combinations, and the J3 formula calculates the difference between the largest group and the smallest group. When that is minimized, you have your "best" combination.

Hope this helps.

 

[meighkee]

Thanks very much Eric. Will take a day or two to put this into work. Greatly appreciate your timely response. Will let you know how it's working for me.

 

[meighkee]

You could use the Solver for something like this. If you don't have the Solver installed, click File > Options > Add-ins > Excel Add-ins (on the bottom) > Go > check the Solver box > OK.

Now set up your sheet like this:

Excel 2012

	A	B	C	D	E	F	G	H	I	J
1	Product	Price	Group		Group1	Group2	Group3	Group4		Difference
2	Product 1	11			0	0	0	0
3	Product 2	68			0	0	0	0		0
4	Product 3	89
5	Product 4	33
6	Product 5	72
7	Product 6	95
8	Product 7	14
9	Product 8	87
10	Product 9	53
11	Product 10	99
12	Product 11	79
13	Product 12	1
14	Product 13	12
15	Product 14	51
16	Product 15	50
17	Product 16	23
18	Product 17	7
19	Product 18	25
20	Product 19	10
21	Product 20	90

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula E2 =COUNTIF($C$2:$C$21,1) F2 =COUNTIF($C$2:$C$21,2) G2 =COUNTIF($C$2:$C$21,3) H2 =COUNTIF($C$2:$C$21,4) E3 =SUMIF($C$2:$C$21,1,$B$2:$B$21) F3 =SUMIF($C$2:$C$21,2,$B$2:$B$21) G3 =SUMIF($C$2:$C$21,3,$B$2:$B$21) H3 =SUMIF($C$2:$C$21,4,$B$2:$B$21) J3 =ABS(MAX(E3:H3)-MIN(E3:H3)) <tbody> </tbody>

<tbody>
</tbody>

Go to the Data tab, click Solver, and set up the problem like this:

Set Objective: J3
To: Min
By Changing Variable cells: C2:C21
Subject to the Constraints:
C2:C21<=4
C2:C21=integer
C2:C21 >=1
E2:H2=5
Solving Method: Evolutionary

Click Solve. The values in C2:C21 will be which group each product should go in. You can probably figure out what all the values are: 5 is the number of items in each group, 4 is the number of groups, etc. The Solver will go through thousands of combinations, and the J3 formula calculates the difference between the largest group and the smallest group. When that is minimized, you have your "best" combination.

Hope this helps.

Good Morning Eric,

When inputting the Solver data, the line - C2:C21=integer - is giving me the following error message when using the word integer as a constraint. "Constraint must be a number, simple reference, or formula with a numeric value." Should I actually be plugging in a cell location rather than using the word 'integer'?

Thanks much,

 

[Eric W]

You don't type the word integer. When you enter the constraint, you put the range in the left box, and there's a drop-down in the middle with the operator. Click the drop-down and select "int".

 

[meighkee]

You don't type the word integer. When you enter the constraint, you put the range in the left box, and there's a drop-down in the middle with the operator. Click the drop-down and select "int".

Can you give me an example of the range?

 

[Eric W]

C2:c21

 

[meighkee]

Excellent Eric. Work perfectly.

Thank you !!!

 

[Eric W]

Glad you got it working!