Using Solver Add-In to work out the Sub-values of a Total

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 File then Options
    Solver options
  • Click on the Add-Ins row on the left-hand side
    Solver Add-In
  • 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
  • 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)
    solver sum product addin
    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

sumproduct solver addin

  • 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.
    Solver Finished
  • The results should have a 1 next to the corresponding Values make up the total 27.29.
    Solver Finished

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload the CAPTCHA.