Windows XP

 Windows 7

Microsoft Excel 2010 : Finding Optimal Solutions by Using Solver

10/22/2012 3:57:49 PM

Tip

It helps to spell out every aspect of your problem so that you can identify the cells you want Solver to use in its calculations.

If you performed a complete installation when you installed Excel on your computer, you see the Solver button on the Data tab in the Analysis group. If not, you need to install the Solver Add-In. To do so, click the File tab, and then click Options. In the Excel Options dialog box, click Add-Ins to display the Add-Ins page. At the bottom of the dialog box, in the Manage list, click Excel Add-Ins, and then click Go to display the Add-Ins dialog box. Select the Solver Add-in check box and click OK to install Solver.

Tip

You might be prompted for the Microsoft Office system installation CD. If so, put the CD in your CD drive, and click OK.

After the installation is complete, Solver appears on the Data tab, in the Analysis group. Clicking Solver displays the Solver Parameters dialog box.

The first step of setting up your Solver problem is to identify the cell that contains the summary formula you want to establish as your objective. To identify that cell, click in the Set Objective box, click the target cell in the worksheet, and then select the option representing whether you want to minimize the cell’s value, maximize the cell’s value, or make the cell take on a specific value. Next, you click in the By Changing Variable Cells box and select the cells Solver should vary to change the value in the objective cell. Finally, you can create constraints that will set the limits for the values Solver can use. To do so, click Add to open the Add Constraint dialog box.

You add constraints to the Solver problem by selecting the cells to which you want to apply the constraint, selecting the comparison operation (such as less than or equal to, greater than or equal to, or must be an integer), and clicking in the Constraint box to select the cell with the value of the constraint. You could also type a value in the Constraint box, but referring to a cell makes it possible for you to change the constraint later without opening Solver.

Tip

After you run Solver, you can use the commands in the Solver Results dialog box to save the results as changes to your worksheet or create a scenario based on the changed data.

In this exercise, you’ll use Solver to determine the best mix of ads given the following constraints:

• You want to maximize the number of people who see the ads.

• You must buy at least 8 ads in 3 magazines and at least 10 in the fourth.

• You can’t buy part of an ad (that is, all numbers must be integers).

• You can buy no more than 20 ads in any one magazine.

• You must reach at least 10,000,000 people.

Set Up

1. If the Solver button doesn’t appear in the Analysis group on the Data tab, follow the instructions from earlier in this section to install it.

2. In the Analysis group on the Data tab, click Solver.

The Solver Parameters dialog box opens.

3. Click in the Set Objective box, and then click cell G9.

\$G\$9 appears in the Set Objective field.

4. Click Max.

5. Click in the By Changing Variable Cells field, and select cells E5:E8.

\$E\$5:\$E\$8 appears in the By Changing Variable Cells field.

The Add Constraint dialog box opens.

7. Select cells E5:E8.

\$E\$5:\$E\$8 appears in the Cell Reference field.

8. In the operator list, click int. Then click Add.

Excel adds the constraint to the Solver problem, and the Add Constraint dialog box clears to accept the next constraint.

Note

In Excel 2010, the Ignore Integer Constraints option is turned on by default. To turn the option off, in the Solver Parameters dialog box, click Options. On the All Methods page of the dialog box, uncheck the Ignore Integer Constraints box and click OK.

9. Click cell F9.

=\$F\$9 appears in the Cell Reference field.

10. Click in the Constraint field, and then click cell G11.

=\$G\$11 appears in the Constraint field.

Excel adds the constraint to the Solver problem, and the Add Constraint dialog box clears to accept the next constraint.

12. Click cell G9.

\$G\$9 appears in the Cell Reference field.

13. In the operator list, click >=.

14. Click in the Constraint field, and then click cell G12.

=\$G\$12 appears in the Constraint field.

Excel adds the constraint to the Solver problem, and the Add Constraint dialog box clears to accept the next constraint.

16. Select cells E5:E7.

\$E\$5:\$E\$7 appears in the Cell Reference field.

17. In the operator list, click >=.

18. Click in the Constraint field, and then click cell G13.

=\$G\$13 appears in the Constraint field.

Excel adds the constraint to the Solver problem, and the Add Constraint dialog box clears to accept the next constraint.

20. Click cell E8.

\$E\$8 appears in the Cell Reference field.

21. In the operator list, click >=.

22. Click in the Constraint field, and then click cell G14.

=\$G\$14 appears in the Constraint field.

Excel adds the constraint to the Solver problem, and the Add Constraint dialog box clears to accept the next constraint.

24. Select cells E5:E8.

\$E\$5:\$E\$8 appears in the Cell Reference field.

25. Verify that the <= operator appears in the operator field, click in the Constraint field, and then click cell G15.

=\$G\$15 appears in the Constraint field.

26. Click OK.

Excel adds the constraint to the Solver problem and closes the Add Constraint dialog box, and the Solver Parameters dialog box opens again.

27. Click Solve.

The Solver Results dialog box opens, indicating that Solver found a solution. The result is displayed in the body of the worksheet.

28. Click Cancel.

The Solver Results dialog box closes.

Clean Up

 Other -----------------

 REVIEW

 VIDEO TUTORIAL

Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8 BlackBerry Android Ipad Iphone iOS
Popular keywords
HOW TO Swimlane in Visio Visio sort key Pen and Touch Creating groups in Windows Server Raid in Windows Server Exchange 2010 maintenance Exchange server mail enabled groups Debugging Tools Collaborating
 Top 10