• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL

Chapter 14. Analyzing Data > Answering Complex Questions with Solver

Answering Complex Questions with Solver

If you have a more complex situation where you need to maximize a value (such as total profits), minimize one (such as total expenses), or arrive at a particular value by changing one or more variables that contribute to the calculation, you can do it with Solver. In our example, we'll be trying to maximize profits by varying advertising expenses.

To maximize or minimize a value using Solver:

Look for Tools > Solver. If it isn't there, install it from the Value Pack, under the Excel Add-Ins section.

Set up your spreadsheet including any formulas (in our example, the formulas are for Profit in row 7 and Totals in column F) (Figure 14.31).

Figure 14.31. Set up your spreadsheet.

Choose Tools > Solver (Figure 14.32).

Figure 14.32. Choose Tools > Solver.

The Solver Parameters dialog box appears (Figure 14.33). Set the target cell you want Solver to focus on, and choose whether you want to maximize, minimize, or arrive at a particular value. Tell Solver which cells to change in order to arrive at the desired value, by entering references to them in the By Changing Cells text box. You may also set constraints on cells; in the example, we know we want to spend at least $25,000 on advertising over the year, so we've constrained total advertising to greater than or equal to that value.

Figure 14.33. Set the Solver parameters.

Click Solve. Excel tries different values for the changing cells until it finds a combination that meets the target criteria and constraints. It puts the answers in the spreadsheet (Figure 14.34) and presents you with options for whether to keep them there or restore the original values (Figure 14.35).

Figure 14.34. The answer appears in the spreadsheet.

Figure 14.35. Choose Keep Solver Solution or Restore Original Values.



Not a subscriber?

Start A Free Trial

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint