Some time back, I wrote a tutorial 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. Fortunately, Microsoft has a more powerful add-in analysis tool called Excel Solver that overcomes these issues. You can even add a constraint to your solution.
What is Excel Solver?
It’s is an optional Excel 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 to find an optimal solution. If you recall my Goal Seek article, my total vote count number increased. That was a problem.
With this enhanced functionality, I can ask Excel 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 to my example. I find adding the color helps me later when I enter variables and values into the Solver Parameters dialog.
[A] Objective – This is a single cell on the active sheet representing my goal. Currently, the value is 63.90%. I want to change this value to the required winning percentage.
[B] Variable – This can be one or more cells that Solver will change to achieve the desired objective. It can be a range of cells or nonadjacent cells. The Solver will run through its calculations by adjusting the YES/NO counts until it finds the optimal solution. You’re allowed up to 200 variable cells.
In my example, I have 2 adjacent values. However, you might have a problem where your variables are nonadjacent. In this case, you can hold down your CTRL key and click or separate the cells by a comma.
[C] Constraint – This is an optional but important cell. As the name suggests, it limits the solution 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. Other constraint types might include integers or minimum values.
The Parameters Dialog
I can take these cell references and use them in 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 with the $ anchors. 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 solutions.
How to Add Excel Solver
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 Excel Solver
These steps below were done using Microsoft 365 (Previously called 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.
- You may need to click the Enable Editing button on the Protected View banner.
- Click cell G3. There is no magic to this cell reference 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. Also, 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 the Next Level
The analysis add-in 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.
You Might Also Like These Excel Resources
Disclaimer: Images from Amazon Product Advertising API. I may receive an affiliate commission on these products if you buy. Updated: 2021-04-18