Assistance with Solver

[KyBoy]

Hi All,

After learning that Goal Seek is limited I started to better understand the Solver Tool.
Can someone help me with the constraints to achieve my goal?

This is my data (paste into A1)

	People		People		Goal %		Current		Delta		Change to People		Number of People to make up Difference
Group A	64		64		5.0000%		11%		-6.27%		0
Group B	32		32		5.0000%		6%		-0.63%		0
Group C	17		17		5.0000%		3%		2.01%		0
Group D	53		53		5.0000%		9%		-4.33%		0
Group E	17		17		5.0000%		3%		2.01%		0
Group F	55		55		5.0000%		10%		-4.68%		0
Group G	58		58		5.0000%		10%		-5.21%		0
Group H	6		6		5.0000%		1%		3.94%		0
Group I	2		2		5.0000%		0%		4.65%		0
Group J	3		3		5.0000%		1%		4.47%		0
Group K	4		4		5.0000%		1%		4.30%		0
Group L	1		1		5.0000%		0%		4.82%		0
Group M	62		62		7.5000%		11%		-3.42%		0
Group N	59		59		7.5000%		10%		-2.89%		0
Group O	9		9		5.0000%		2%		3.42%		0
Group P	36		36		5.0000%		6%		-1.34%		0
Group Q	21		21		5.0000%		4%		1.30%		0
Group R	22		22		5.0000%		4%		1.13%		0
Group S	47		47		5.0000%		8%		-3.27%		0
	568		568		100%		100%		0%

<colgroup><col span="2"><col><col span="2"><col><col><col><col><col span="6"></colgroup><tbody>
</tbody>

Formulas

People

People

Goal %

Current

Delta

Change to People

Number of People to make up Difference

Group A

64

=B2+L2

0.05

=D2/$D$21

=F2-H2

0

Group B

32

=B3+L3

0.05

=D3/$D$21

=F3-H3

0

Group C

17

=B4+L4

0.05

=D4/$D$21

=F4-H4

0

Group D

53

=B5+L5

0.05

=D5/$D$21

=F5-H5

0

Group E

17

=B6+L6

0.05

=D6/$D$21

=F6-H6

0

Group F

55

=B7+L7

0.05

=D7/$D$21

=F7-H7

0

Group G

58

=B8+L8

0.05

=D8/$D$21

=F8-H8

0

Group H

6

=B9+L9

0.05

=D9/$D$21

=F9-H9

0

Group I

2

=B10+L10

0.05

=D10/$D$21

=F10-H10

0

Group J

3

=B11+L11

0.05

=D11/$D$21

=F11-H11

0

Group K

4

=B12+L12

0.05

=D12/$D$21

=F12-H12

0

Group L

1

=B13+L13

0.05

=D13/$D$21

=F13-H13

0

Group M

62

=B14+L14

0.075

=D14/$D$21

=F14-H14

0

Group N

59

=B15+L15

0.075

=D15/$D$21

=F15-H15

0

Group O

9

=B16+L16

0.05

=D16/$D$21

=F16-H16

0

Group P

36

=B17+L17

0.05

=D17/$D$21

=F17-H17

0

Group Q

21

=B18+L18

0.05

=D18/$D$21

=F18-H18

0

Group R

22

=B19+L19

0.05

=D19/$D$21

=F19-H19

0

Group S

47

=B20+L20

0.05

=D20/$D$21

=F20-H20

0

=SUM(B2:B20)

=SUM(D2:D20)

=SUM(F2:F20)

=SUM(H2:H20)

=SUM(J2:J20)

<colgroup><col span="2"><col><col span="2"><col><col><col><col><col span="6"></colgroup><tbody>
</tbody>

My goal:
- Utilize column G to add/subtract people from each group to reach the target Goal %
- Keep the original total in cell D21 to 568

 

[Worf]

Hi

o See if the example below helps.
o Solver has an option for integer solutions, but it was not converging.
o The screenshot could not show all the constraints. All formulas at column M are set to a value less than 2/10.

Sheet1

	F	G	H	I	J	K	L	M
43			People	Goal	Inferior Limit	Superior Limit	Result	Difference
44		Group A	56,93218	0,1	46,8	66,8	57	0,1321806
45		Group B	113,7828	0,2	103,6	123,6	114	0,1828125
46		Group C	170,35	0,3	160,4	180,4	170	0,050001
47		Group D	198,9322	0,35	188,8	208,8	199	0,1321806
48		Group E	28,4375	0,05	18,4	38,4	28	0,0375
49			568,4347	1			568
50
51	Desired Total	568

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:88px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:90px;"><col style="width:91px;"><col style="width:64px;"><col style="width:71px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula J44 =I44*G51-10 K44 =I44*G51+10 L44 =ROUND(H44,0) M44 =ABS(H44-I44*$G$51) J45 =I45*G51-10 K45 =I45*G51+10 L45 =ROUND(H45,0) M45 =ABS(H45-I45*$G$51) J46 =I46*G51-10 K46 =I46*G51+10 L46 =ROUND(H46,0) M46 =ABS(H46-I46*$G$51) J47 =I47*G51-10 K47 =I47*G51+10 L47 =ROUND(H47,0) M47 =ABS(H47-I47*$G$51) J48 =I48*G51-10 K48 =I48*G51+10 L48 =ROUND(H48,0) M48 =ABS(H48-I48*$G$51) H49 =SUM(H44:H48) I49 =SUM(I44:I48) L49 =SUM(L44:L48) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4



<strike></strike>

<strike></strike>