Identify numbers in sum using Excel solver
24,
Apr
2022
COM
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
- Press with left mouse button on Office button
- Press with left mouse button on Excel Options
- Press with left mouse button on add-ins
- Install Solver
- Press with left mouse button on OK
Interested in what other things Excel Solver can do? See category for Solver.
Setup formula
- Double press with left mouse button on B12
- Type =SUMPRODUCT(B1:B11,A1:A11) + ENTER
Start Excel 2007 Solver
- Press with left mouse button on "Data" in the Ribbon
- Press with left mouse button on "Solver"
- Press with left mouse button on "Set Target Cell:" button
- Press with left mouse button on B12
- Type in "Value of:" window the summed value 2615,4
- Press with left mouse button on "By Changing Cells:" button and select B1:B11.
- Press with left mouse button on "Add" button
- Press with left mouse button on "Cell Reference:" button and select B1:B11
- Select "bin" in drop down menu (triangle)
- Press with left mouse button on "OK"
- Press with left mouse button on "Options"
- 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. - I changed "Max Time:" to 1000 seconds
- Press with left mouse button on OK
- Press with left mouse button on "Solve"A couple of minutes later.
- Press with left mouse button on OK!
Subscribe to:
Posts (Atom)