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)

[TABLE="width: 826"]
<colgroup><col span="2"><col><col span="2"><col><col><col><col><col span="6"></colgroup><tbody>[TR]
[TD][/TD]
[TD]People[/TD]
[TD][/TD]
[TD]People[/TD]
[TD][/TD]
[TD]Goal %[/TD]
[TD][/TD]
[TD]Current[/TD]
[TD][/TD]
[TD]Delta[/TD]
[TD][/TD]
[TD="colspan: 2"]Change to People[/TD]
[TD][/TD]
[TD]Number of People to make up Difference[/TD]
[/TR]
[TR]
[TD]Group A[/TD]
[TD]64[/TD]
[TD][/TD]
[TD]64[/TD]
[TD][/TD]
[TD]5.0000%[/TD]
[TD][/TD]
[TD="align: right"]11%[/TD]
[TD][/TD]
[TD="align: right"]-6.27%[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group B[/TD]
[TD]32[/TD]
[TD][/TD]
[TD]32[/TD]
[TD][/TD]
[TD]5.0000%[/TD]
[TD][/TD]
[TD="align: right"]6%[/TD]
[TD][/TD]
[TD="align: right"]-0.63%[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group C[/TD]
[TD]17[/TD]
[TD][/TD]
[TD]17[/TD]
[TD][/TD]
[TD]5.0000%[/TD]
[TD][/TD]
[TD="align: right"]3%[/TD]
[TD][/TD]
[TD="align: right"]2.01%[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group D[/TD]
[TD]53[/TD]
[TD][/TD]
[TD]53[/TD]
[TD][/TD]
[TD]5.0000%[/TD]
[TD][/TD]
[TD="align: right"]9%[/TD]
[TD][/TD]
[TD="align: right"]-4.33%[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group E[/TD]
[TD]17[/TD]
[TD][/TD]
[TD]17[/TD]
[TD][/TD]
[TD]5.0000%[/TD]
[TD][/TD]
[TD="align: right"]3%[/TD]
[TD][/TD]
[TD="align: right"]2.01%[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group F[/TD]
[TD]55[/TD]
[TD][/TD]
[TD]55[/TD]
[TD][/TD]
[TD]5.0000%[/TD]
[TD][/TD]
[TD="align: right"]10%[/TD]
[TD][/TD]
[TD="align: right"]-4.68%[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group G[/TD]
[TD]58[/TD]
[TD][/TD]
[TD]58[/TD]
[TD][/TD]
[TD]5.0000%[/TD]
[TD][/TD]
[TD="align: right"]10%[/TD]
[TD][/TD]
[TD="align: right"]-5.21%[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group H[/TD]
[TD]6[/TD]
[TD][/TD]
[TD]6[/TD]
[TD][/TD]
[TD]5.0000%[/TD]
[TD][/TD]
[TD="align: right"]1%[/TD]
[TD][/TD]
[TD="align: right"]3.94%[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group I[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD]5.0000%[/TD]
[TD][/TD]
[TD="align: right"]0%[/TD]
[TD][/TD]
[TD="align: right"]4.65%[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group J[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD]5.0000%[/TD]
[TD][/TD]
[TD="align: right"]1%[/TD]
[TD][/TD]
[TD="align: right"]4.47%[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group K[/TD]
[TD]4[/TD]
[TD][/TD]
[TD]4[/TD]
[TD][/TD]
[TD]5.0000%[/TD]
[TD][/TD]
[TD="align: right"]1%[/TD]
[TD][/TD]
[TD="align: right"]4.30%[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group L[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]5.0000%[/TD]
[TD][/TD]
[TD="align: right"]0%[/TD]
[TD][/TD]
[TD="align: right"]4.82%[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group M[/TD]
[TD]62[/TD]
[TD][/TD]
[TD]62[/TD]
[TD][/TD]
[TD]7.5000%[/TD]
[TD][/TD]
[TD="align: right"]11%[/TD]
[TD][/TD]
[TD="align: right"]-3.42%[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group N[/TD]
[TD]59[/TD]
[TD][/TD]
[TD]59[/TD]
[TD][/TD]
[TD]7.5000%[/TD]
[TD][/TD]
[TD="align: right"]10%[/TD]
[TD][/TD]
[TD="align: right"]-2.89%[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group O[/TD]
[TD]9[/TD]
[TD][/TD]
[TD]9[/TD]
[TD][/TD]
[TD]5.0000%[/TD]
[TD][/TD]
[TD="align: right"]2%[/TD]
[TD][/TD]
[TD="align: right"]3.42%[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group P[/TD]
[TD]36[/TD]
[TD][/TD]
[TD]36[/TD]
[TD][/TD]
[TD]5.0000%[/TD]
[TD][/TD]
[TD="align: right"]6%[/TD]
[TD][/TD]
[TD="align: right"]-1.34%[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group Q[/TD]
[TD]21[/TD]
[TD][/TD]
[TD]21[/TD]
[TD][/TD]
[TD]5.0000%[/TD]
[TD][/TD]
[TD="align: right"]4%[/TD]
[TD][/TD]
[TD="align: right"]1.30%[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group R[/TD]
[TD]22[/TD]
[TD][/TD]
[TD]22[/TD]
[TD][/TD]
[TD]5.0000%[/TD]
[TD][/TD]
[TD="align: right"]4%[/TD]
[TD][/TD]
[TD="align: right"]1.13%[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group S[/TD]
[TD]47[/TD]
[TD][/TD]
[TD]47[/TD]
[TD][/TD]
[TD]5.0000%[/TD]
[TD][/TD]
[TD="align: right"]8%[/TD]
[TD][/TD]
[TD="align: right"]-3.27%[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]568[/TD]
[TD][/TD]
[TD]568[/TD]
[TD][/TD]
[TD]100%[/TD]
[TD][/TD]
[TD="align: right"]100%[/TD]
[TD][/TD]
[TD="align: right"]0%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Formulas
[TABLE="width: 1652"]
<colgroup><col span="2"><col><col span="2"><col><col><col><col><col span="6"></colgroup><tbody>[TR]
[TD][/TD]
[TD]People[/TD]
[TD][/TD]
[TD]People[/TD]
[TD][/TD]
[TD]Goal %[/TD]
[TD][/TD]
[TD]Current[/TD]
[TD][/TD]
[TD]Delta[/TD]
[TD][/TD]
[TD]Change to People[/TD]
[TD] [/TD]
[TD][/TD]
[TD]Number of People to make up Difference[/TD]
[/TR]
[TR]
[TD]Group A[/TD]
[TD]64[/TD]
[TD][/TD]
[TD]=B2+L2[/TD]
[TD][/TD]
[TD]0.05[/TD]
[TD][/TD]
[TD]=D2/$D$21[/TD]
[TD][/TD]
[TD]=F2-H2[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group B[/TD]
[TD]32[/TD]
[TD][/TD]
[TD]=B3+L3[/TD]
[TD][/TD]
[TD]0.05[/TD]
[TD][/TD]
[TD]=D3/$D$21[/TD]
[TD][/TD]
[TD]=F3-H3[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group C[/TD]
[TD]17[/TD]
[TD][/TD]
[TD]=B4+L4[/TD]
[TD][/TD]
[TD]0.05[/TD]
[TD][/TD]
[TD]=D4/$D$21[/TD]
[TD][/TD]
[TD]=F4-H4[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group D[/TD]
[TD]53[/TD]
[TD][/TD]
[TD]=B5+L5[/TD]
[TD][/TD]
[TD]0.05[/TD]
[TD][/TD]
[TD]=D5/$D$21[/TD]
[TD][/TD]
[TD]=F5-H5[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group E[/TD]
[TD]17[/TD]
[TD][/TD]
[TD]=B6+L6[/TD]
[TD][/TD]
[TD]0.05[/TD]
[TD][/TD]
[TD]=D6/$D$21[/TD]
[TD][/TD]
[TD]=F6-H6[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group F[/TD]
[TD]55[/TD]
[TD][/TD]
[TD]=B7+L7[/TD]
[TD][/TD]
[TD]0.05[/TD]
[TD][/TD]
[TD]=D7/$D$21[/TD]
[TD][/TD]
[TD]=F7-H7[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group G[/TD]
[TD]58[/TD]
[TD][/TD]
[TD]=B8+L8[/TD]
[TD][/TD]
[TD]0.05[/TD]
[TD][/TD]
[TD]=D8/$D$21[/TD]
[TD][/TD]
[TD]=F8-H8[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group H[/TD]
[TD]6[/TD]
[TD][/TD]
[TD]=B9+L9[/TD]
[TD][/TD]
[TD]0.05[/TD]
[TD][/TD]
[TD]=D9/$D$21[/TD]
[TD][/TD]
[TD]=F9-H9[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group I[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]=B10+L10[/TD]
[TD][/TD]
[TD]0.05[/TD]
[TD][/TD]
[TD]=D10/$D$21[/TD]
[TD][/TD]
[TD]=F10-H10[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group J[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]=B11+L11[/TD]
[TD][/TD]
[TD]0.05[/TD]
[TD][/TD]
[TD]=D11/$D$21[/TD]
[TD][/TD]
[TD]=F11-H11[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group K[/TD]
[TD]4[/TD]
[TD][/TD]
[TD]=B12+L12[/TD]
[TD][/TD]
[TD]0.05[/TD]
[TD][/TD]
[TD]=D12/$D$21[/TD]
[TD][/TD]
[TD]=F12-H12[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group L[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]=B13+L13[/TD]
[TD][/TD]
[TD]0.05[/TD]
[TD][/TD]
[TD]=D13/$D$21[/TD]
[TD][/TD]
[TD]=F13-H13[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group M[/TD]
[TD]62[/TD]
[TD][/TD]
[TD]=B14+L14[/TD]
[TD][/TD]
[TD]0.075[/TD]
[TD][/TD]
[TD]=D14/$D$21[/TD]
[TD][/TD]
[TD]=F14-H14[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group N[/TD]
[TD]59[/TD]
[TD][/TD]
[TD]=B15+L15[/TD]
[TD][/TD]
[TD]0.075[/TD]
[TD][/TD]
[TD]=D15/$D$21[/TD]
[TD][/TD]
[TD]=F15-H15[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group O[/TD]
[TD]9[/TD]
[TD][/TD]
[TD]=B16+L16[/TD]
[TD][/TD]
[TD]0.05[/TD]
[TD][/TD]
[TD]=D16/$D$21[/TD]
[TD][/TD]
[TD]=F16-H16[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group P[/TD]
[TD]36[/TD]
[TD][/TD]
[TD]=B17+L17[/TD]
[TD][/TD]
[TD]0.05[/TD]
[TD][/TD]
[TD]=D17/$D$21[/TD]
[TD][/TD]
[TD]=F17-H17[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group Q[/TD]
[TD]21[/TD]
[TD][/TD]
[TD]=B18+L18[/TD]
[TD][/TD]
[TD]0.05[/TD]
[TD][/TD]
[TD]=D18/$D$21[/TD]
[TD][/TD]
[TD]=F18-H18[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group R[/TD]
[TD]22[/TD]
[TD][/TD]
[TD]=B19+L19[/TD]
[TD][/TD]
[TD]0.05[/TD]
[TD][/TD]
[TD]=D19/$D$21[/TD]
[TD][/TD]
[TD]=F19-H19[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group S[/TD]
[TD]47[/TD]
[TD][/TD]
[TD]=B20+L20[/TD]
[TD][/TD]
[TD]0.05[/TD]
[TD][/TD]
[TD]=D20/$D$21[/TD]
[TD][/TD]
[TD]=F20-H20[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]=SUM(B2:B20)[/TD]
[TD][/TD]
[TD]=SUM(D2:D20)[/TD]
[TD][/TD]
[TD]=SUM(F2:F20)[/TD]
[TD][/TD]
[TD]=SUM(H2:H20)[/TD]
[TD][/TD]
[TD]=SUM(J2:J20)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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
Desired Total

<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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]43[/TD]

[TD="align: center"]People[/TD]
[TD="align: center"]Goal[/TD]
[TD="align: center"]Inferior Limit[/TD]
[TD="align: center"]Superior Limit[/TD]
[TD="align: center"]Result[/TD]
[TD="align: center"]Difference[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]44[/TD]

[TD="align: center"]Group A[/TD]
[TD="align: center"]56,93218[/TD]
[TD="align: center"]0,1[/TD]
[TD="align: center"]46,8[/TD]
[TD="align: center"]66,8[/TD]
[TD="align: center"]57[/TD]
[TD="align: center"]0,1321806[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]45[/TD]

[TD="align: center"]Group B[/TD]
[TD="align: center"]113,7828[/TD]
[TD="align: center"]0,2[/TD]
[TD="align: center"]103,6[/TD]
[TD="align: center"]123,6[/TD]
[TD="align: center"]114[/TD]
[TD="align: center"]0,1828125[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]46[/TD]

[TD="align: center"]Group C[/TD]
[TD="align: center"]170,35[/TD]
[TD="align: center"]0,3[/TD]
[TD="align: center"]160,4[/TD]
[TD="align: center"]180,4[/TD]
[TD="align: center"]170[/TD]
[TD="align: center"]0,050001[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]47[/TD]

[TD="align: center"]Group D[/TD]
[TD="align: center"]198,9322[/TD]
[TD="align: center"]0,35[/TD]
[TD="align: center"]188,8[/TD]
[TD="align: center"]208,8[/TD]
[TD="align: center"]199[/TD]
[TD="align: center"]0,1321806[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]48[/TD]

[TD="align: center"]Group E[/TD]
[TD="align: center"]28,4375[/TD]
[TD="align: center"]0,05[/TD]
[TD="align: center"]18,4[/TD]
[TD="align: center"]38,4[/TD]
[TD="align: center"]28[/TD]
[TD="align: center"]0,0375[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]49[/TD]

[TD="align: center"]568,4347[/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]568[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]50[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]51[/TD]

[TD="align: center"]568[/TD]

</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>