Some time back, I wrote an article about a useful function in Microsoft Excel called Goal Seek. It quickly allows you to solve easy “what-if” scenarios. Although helpful, it comes with limitations. However, Microsoft has a more powerful add-in analysis tool called Excel Solver that overcomes these issues. You can even add a constraint.
What is Excel Solver?
Solver is an optional add-in designed to do more complex “what-if” calculations using different solving methods. The main advantage is Solver allows you to work with variables and constraints. If you recall my Goal Seek article, my total vote count number increased.
With this enhanced functionality, I can ask Solver to tell me how the YES/NO vote count needed to change to win an election while not altering the total vote count.
To solve this problem, you need to define certain cell types. I’ve added background color and callouts on my example. I find adding the color helps me later on when I enter variables and values into the Solver Parameters dialog.
[A] Objective – This is a single cell on the active sheet that will represent my goal. Currently, the value is 63.90%. I want to change this to the required winning percentage. This objective cell must contain a formula.
[B] Variable – This can be one or more cells that Solver will change to get to the desired objective. It can be a range of cells or nonadjacent cells. Solver will run through its calculations by adjusting the YES/NO counts until it finds the optimum solution.
In my example, I have 2 adjacent values. However, you might have a scenario where your variables nonadjacent. In this case, you can hold down your CTRL key and click or separate them by a comma.
[C] Constraint – This is an optional cell. As the name suggests, it limits the cell to certain criteria. Your spreadsheet can have more than one constraint. In our case, the TOTAL vote count has to stay the same. No stuffing the ballot box with additional YES votes;-)
The Solver Parameters Dialog
I can take these cell references and use them into the Solver Parameters dialog as shown below.
The first field is the Set Objective. Once you click in the field, click your desired Excel cell and it will carry over as an absolute reference. In earlier versions of Solver, Excel called this field the target cell.
Underneath you’ll see a To: line with three radio buttons. In my scenario, I want to test for a Value Of 66.7%. However, I can’t type a % sign in this field so I’ll use the decimal value.
Now that we’ve told Excel our objective is to get 66.7% of the YES votes, I need to define my variables and which cells can be changed. Again, I clicked in the By Changing Variable Cells: field and then highlighted cells C21:C22.
The last item I defined was my constraint. This is done by clicking the Add button on the lower right side. If you look under the Subject to the Constraints: section, you’ll see I’m telling Excel to keep the total vote count at 7008.
I also kept the default Solving Method of GRG Nonlinear. However, there are more complex methods using linear programming.
How to Add Solver to Excel
I’m not sure the reasoning, but Solver in Excel isn’t installed by default. It’s sort of a hidden feature.
- Open Microsoft Excel.
- From the File menu, select Options.
- In the Excel Options dialog, click Add-ins from the left menu.
- Look down to the bottom right side for a field called Manage:
- Select Excel Add-ins from the drop-down list.
- Click the Go… button.
- From the Add-ins dialog, check the box for Solver Add-in.
- Click OK.
If you go to your Data tab, you should now see Solver in the Analyze section.
How to Use Solver
These steps below were done using Microsoft Office 365 and the sample spreadsheet. The spreadsheet has two versions of the election results so you can use Solver on one and view the original results. Once you learn the tool, you can skip adding a second version.
- Download and open the excel-solver worksheet.
- Click cell G3. There is no magic to this cell except that it opens Solver closer to my data.
- Click the Data tab.
- From the Analyze group, click Solver.
- The Solver Parameters dialog appears. You can reposition it on your screen.
- Click in the Set Objective box and then click cell D4.
- Toggle the Value Of: radio button.
- Replace the “0” value with 0.667.
- Click in the By Changing Variable Cells: field.
- Highlight cells C4:C5. Excel will convert these to an absolute reference,
- Move down to the Constraints area and click the Add button on the right. The Add Constraint dialog opens.
- Click in the Cell Reference field.
- Click cell C6. Excel will automatically populate the field.
- Move to the next field and select = from the drop-down menu.
- In the Constraint: field type 7008 since we want to match the total votes count.
- Click the OK button since we only have one constraint.
- Click the Solve button. Excel will attempt to find the optimal solution.
- The Solver Results dialog opens and your spreadsheet values in C4:C5 should’ve changed. In addition, the Total % has increased to our target value of 66.70%.
- Click OK to accept the new solution.
If you do the math, you can see this ballot measure could’ve had a different outcome if 196 people had voted “yes” instead of “no”. Or, if more people voted.
Taking Solver to the Next Level
Solver can do some sophisticated analysis using the other radio button options like Max and Min. In fact, Microsoft has a tutorial on using Solver to determine the optimal product mix. It’s much more detailed than this article and includes using SUMPRODUCT and a linear solving method.
As you explore and test, you’ll discover more options than this simple example. Solver allows you to also load and save optimization scenarios. It also has a report section that provides additional details.
Disclaimer: Images from Amazon Product Advertising API. This site may receive an affiliate commission on these products. Updated: 2020-03-28