Identify numbers in sum using Excel solver

0
COM
· Posted by

original source: https://www.get-digital-help.com/identify-numbers-in-sum-using-solver-in-excel/ 

Author: Oscar Cronquist Article last updated on February 24, 2018

Here is a useful feature I recently found googling for Excel solver examples.

I have summed some random values from the list A1:A11 in cell C1.

How do I find those summed numbers in C1?

I am going to use Excel Solver. Using more than 20 values (Column A) is not recommended unless you have a lot of spare time.

Install Solver add-in

  1. Press with left mouse button on Office button
  2. Press with left mouse button on Excel Options
  3. Press with left mouse button on add-ins
  4. Install Solver
  5. Press with left mouse button on OK

Interested in what other things Excel Solver can do? See category for Solver.

Setup formula

  1. Double press with left mouse button on B12
  2. Type =SUMPRODUCT(B1:B11,A1:A11) + ENTER

Start Excel 2007 Solver

  1. Press with left mouse button on "Data" in the Ribbon
  2. Press with left mouse button on "Solver"
  3. Press with left mouse button on "Set Target Cell:" button
  4. Press with left mouse button on B12
  5. Type in "Value of:" window the summed value 2615,4
  6. Press with left mouse button on "By Changing Cells:" button and select B1:B11.
  7. Press with left mouse button on "Add" button
  8. Press with left mouse button on "Cell Reference:" button and select B1:B11
  9. Select "bin" in drop down menu (triangle)
  10. Press with left mouse button on "OK"
  11. Press with left mouse button on "Options"
  12. Enable "Assume Linear Model"
    If you have a newer Excel version and can't find "Assume Linear Model", select Simplex LP in "Select s Solving Method" drop down list.
    Then press with left mouse button on "Solve"button. 
    You are ready, ignore steps below.
  13. I changed "Max Time:" to 1000 seconds
  14. Press with left mouse button on OK
  15. Press with left mouse button on "Solve"A couple of minutes later.
  16. Press with left mouse button on OK!

Subscribe to: Posts (Atom)