The solver add-in is a handy plugin that is used to fill in the blanks for formulas that would usually require trial and error to workout. In this example I will be using it to evaluate which values within a column make up a user defined total.
Adding the Solver
- Click on the Add-Ins row on the left-hand side
- Click Solver Add-in, then hit Go…
- Tick the check-box for Solver Add-In when the list of Add-Ins available appears and click OK
- Solver will now be visible under the Data tab in the Analysis part of the tool-strip
Using the Solver
- Leave a blank column next to the sub-values column
- Create a SUMPRODUCT formula that will use your sub-value column and your blank column. E.g. =SUMPRODUCT(A2:A11, B2:B11)
This tells excel to multiply (A1*B1) + (A2 * B2) + (A3 * B3) +…….(A11 * B11)
The formula will return a “0” at the moment because the second column must be blank.
- Click on the data tab then click Solver
- Set your Object to the address of your SUMIF cell
- Make sure the second radio button is set to “Value Of:” and set the text-box to the total value figure.
- This operation only requires one constraint, click “Add” and select the blank values column, in this example it is column B. Change the Operator sign to “bin” and click OK. Now only 1’s or 0’s are allowed in the second column to complete the SUMPRODUCT formula.
- Click Solve, the add-in will then sift through all the potential matches according to your constraints and criteria to work out which values in Column B require a 1.
- When the solver is finished you will get the below screen, click OK.
- The results should have a 1 next to the corresponding Values make up the total 27.29.