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.

Watch a video

Install Solver add-in

  1. Click Office button
  2. Click Excel Options
  3. Click add-ins
  4. Install Solver
  5. Click OK

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

Setup formula

  1. Double click B12
  2. Type =SUMPRODUCT(B1:B11,A1:A11) + ENTER

Recommended reading:

How to use Excel SUMPRODUCT function

Multiplies cell ranges and then sum all values.

Comments(2) Filed in category: Excel, SUMPRODUCT function

Start Excel 2007 Solver

  1. Click "Data" in the Ribbon
  2. Click "Solver"
  3. Click "Set Target Cell:" button
  4. Click B12
  5. Type in "Value of:" window the summed value 2615,4
  6. Click "By Changing Cells:" button and select B1:B11.
  7. Click "Add" button
  8. Click "Cell Reference:" button and select B1:B11
  9. Select "bin" in drop down menu (triangle)
  10. Click "OK"
  11. Click "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 click "Solve"button
    You are ready, ignore steps below.
  13. I changed "Max Time:" to 1000 seconds
  14. Click OK
  15. Click "Solve"A couple of minutes later.
  16. Click OK!

Recommended articles

Excel udf: Find numbers in sum

This article describes how to find a sum from a range of numbers using a user defined function. Let´s see […]

Comments(2) Filed in category: Excel, Finance, SUM function

Find numbers closest to sum

Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […]

Comments(13) Filed in category: Excel, MMULT function, Permutations, SUM function

Excel udf: Find positive and negative amounts that net to zero

I created/modified an udf to solve his question: Find positive and negative amounts that net to zero in excel Question:I would […]

Comments(5) Filed in category: Excel, SUM function