## Identify numbers in sum using solver in excel

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 **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

- Click Office button
- Click Excel Options
- Click add-ins
- Install Solver
- Click OK

### Setup formula

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

### Start Excel 2007 Solver

- Click "Data" in the Ribbon
- Click "Solver"

- Click "Set Target Cell:" button
- Click B12
- Type in "Value of:" window the summed value 2615,4
- Click "By Changing Cells:" button and select B1:B11.
- Click "Add" button

- Click "Cell Reference:" button and select B1:B11
- Select "bin" in drop down menu (triangle)

- Click "OK"

- Click "Options"

- Enable "Assume Linear Model"
- I changed "Max Time:" to 1000 seconds
- Click OK

- Click "Solve"A couple of minutes later.
- Click OK!

### External resources

### 40 Responses to “Identify numbers in sum using solver in excel”

### Leave a Reply

**How to add vba code to your comment:**

[vb 1="vbnet" language=","]

your code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

[...] Let´s see how quickly excel solver finds the numbers using the instructions in this post: Identify numbers in sum using solver in excel. [...]

I found this solution of idendtify number in sum using solver in excel. its great. but i couldnt find excel solver in my office 2010.

i have did the same to install solver add in in excel option and then click on solver add in and then okay.

but later i went to Data ribbon and couldnt find Solver option.

Could you please tell me what is the problem in my office 2010

It's in DATA section

I found this:

http://www.addictivetips.com/windows-tips/microsoft-office-excel-2010-solver-add-in/

Solver is located under the Data tab as shown in the screenshot below.There seems to be something wrong with your excel version. I am using excel 2007 and I have no clue.

Sir;

many many thanks for your unmatched articles provided to the public, you saved my previous time at all

Once again i am thankful to you and my best wishes to you always

Thank you! My data set is 362 cells. It errors me with 'too many variable cells!'

Melynda,

Yes, it would take (almost) forever to calculate 362 cells.

You cannot use more than 100 variables when trying to find the sum of variables using solver. Also cannot have more than 200 constraints.

Thank you so much for sharing this!

It helped me a great deal instead of having to find all the possible combinations to get the value i require.

Thanks!

Sherlyn,

Thank you for commenting!

Fantastic solution, thanks!

is there any other formula to this operation?

Rey,

Only a custom function:

Excel udf: Find numbers in sum

Thank you so much! This is exactly what I was looking for. What a time saver! Would love to have this run with a click of a button with some VBA, looks like my next see if its feasible project.

Very Handy.

Thanks a lot! It is indeed a time saver...

I am happy you like it!

To be completely honest, I didn’t like it! I simply LOVE IT!

I always knew that Excel should be able to do this, but never realized how I could do it on Excel, so thanks a lot for sharing it!

I’m still trying to figure it out a way to have this method on a template, where I won’t need to call and setup the solver, whenever I need to use it.

Anyway thank you very much for the time you took to share with the community.

Cheers from Portugal

Ricardo,

Thank you for your kind words!

I’m still trying to figure it out a way to have this method on a template, where I won’t need to call and setup the solver, whenever I need to use it.Read this post:

Excel udf: Find numbers in sum

Hello,

I have a range with around 100 cells. Is there anyway to do a range that large?

David,

Not that I know of.

I have data in 43 cells and still it is taking forever..is there any wayarouond any macro to help me with this

Vivek Singh,

No, not that I know of.

You can use Excel add-in called SumMatch. It will show all possible combinations, not just one.

Hi, Thanx for the informative article. However, the one major problem in using this solver function is that it takes a lot of time for finding the optimum solution if the quantum of data is huge.

In my field of work there are upto 10,000 rows and 5,000 colomns.. Hence finding a particular amount from such a huge database become difficult for excel.

Abhijit,

Yes, you are right.

Too Many adjustable cell... error said.

what to do next sir?

[…] Hope this helps: Identify numbers in sum using solver in excel | Get Digital Help - Microsoft Excel resource […]

Thank-you for this fantastic solution. You just saved me a ton of time trying to figure out variables that don't work for a solution. I tested this with numbers I knew would work and it's perfect! Finally, a use for Solver in my work.

Hi oscar

Im using excel 2010, my main problem is that whenever i set the solver and it validates a range of around 20 cells the number i get after i hit solve is not the exact target number i put. Concerning the binaries that give us the ability to identify the combination of numbers is giving me 0.27 for some numbers. I can't identify the problem, can it be that our client just paid wrong invoices? Or maybe a mistake have happened?

Than you in advance

Hi,

This is a neat solution to finding values used in a SUM function, but it does not take into account if there are 2 or more possible combinations of values which can be used to achieve the SUM amount. I'm very interested to find out if there is a method of identifying all possible combinations of values. Also, what happens using this method if there are duplicate values in the list of numbers.

Ron,

read this post: http://www.get-digital-help.com/2011/02/25/excel-udf-find-positive-and-negative-amounts-that-net-to-zero/

thank you very much!

I need a method, So when the sum of the values is not equal to the value in "Value of", then it gives me the sum of values less than the value in "value of"

Thank You..

I need to help to try to solve a problem using Excel Solver.

I work for a manufacturing company and we make cores in heights totalling from 10 to 300mm.

We can make individual cores in the following heights

10

15

20

25

30

35

40

50mm

If we receive an order for a core of over 50mm in height, say 75mm height, then we can "stack" two or more cores with say a 50 & 25mm or 40 and 35mm.

I would like to use solver to identify possible combinations using the fewest number of cores to produce the stack.

The only other constraint is that as well as wishing to use as few cores as possible, I would then also like to minimize the different combinations. So to achieve a core of 105mm in height I could use 2 x 40mm cores plus 1 x 25mm but I would prefer using 3 x 35mm as it means I am using all the same size.

Similarly for larger cores it would be better to use two different types and three etc.

Hope this makes sense.

Thanks

Ian

Ian,

Interesting question, unfortunately excel hangs after a while. I have tried multiple excel versions and computers. I don't know why.

I tried this:

Formula in cell D2: =C2*B2

Copy cell D2 and paste to cell range D3:D9

Formula in cell E2: =(C2>0)*1

Copy cell E2 and paste to cell range E3:E9

Formula in cell D10: =SUM(D2:D9)

Formula in cell E10: =SUM(E2:E9)

These are my settings in Solver:

Oscar

Thanks so much for taking the time to look at this. I replicated all your entries but I got the following warning message:

"Lower and upper bounds on variables allow no feasible solutionSome upper bound on a Variable Cell is smaller than a lower bound on that same Variable Cell."

Any ideas?

NB Wasn't sure why "Value Of: 70" although like you I then selected "Min"

Ian,

I replicated all your entries but I got the following warning message:"Lower and upper bounds on variables allow no feasible solutionSome upper bound on a Variable Cell is smaller than a lower bound on that same Variable Cell."

I think you got the less than and greater than signs wrong? Check these constraint lines again.

$C$2:$C$9 < = 10 $C$2:$C$9 >= 0

NB Wasn't sure why "Value Of: 70" although like you I then selected "Min"Sorry, that was an old value I forgot to delete.

I tried with a solution I know works but it is not the optimal solution and let the solver work from there. It then finds another solution but unfortunately not the best solution.

If I try again, it tells me:

Solver has performed 5 iterations for which the objective did not move significantly. Try a smaller convergence setting, or a different starting point.Unfortunately it won't find the best solution.

The solver displays a message on the statusbar for each iteration, this slows down the calculations considerably and I believe it was done on purpose. They want you to buy their products.

Thanks again Oscar.

I re-typed my formula's to make sure I input them correctly.

I input 250 in d12 and hit solver expecting solver to tell me 5x50 i.e. 5 in C9. Is that correct?

But solver runs and after 28K+ subproblems, simply tells me

"Solver cannot improve the current solution. All constraints are satisfied."

Ian,

I input 250 in d12 and hit solver expecting solver to tell me 5x50 i.e. 5 in C9. Is that correct?Yes, this is what I get:

Not the optimal solution.

I am using solver method: Evolutionary

However, if I change to solver method: GRG NonLinear

and start with zeros in cell range C2:C9

I get the optimal solution, cell C9 = 5

If I input 105 in d12 I also get the optimal solution, c7 = 3, solver method: GRG NonLinear

I also tried 115 and 160 in d12 and they all return the optimal solution, if I use GRG NonLinear.

I tried 125 and got this:

and this telling me to change integer tolerance to 0% in the options.

http://www.solver.com/excel-solver-integer-constraints-and-integer-optimality-tolerance

I changed that option.

but solver returned the same solution, not the optimal solution.

The optimal solution is 2x50 + 25 or 3x35 + 20, there are perhaps more.

Great solution, very useful.