Find cheapest paint order with 'Solver'

**Cidona** · 01-12-2012, 04:46 AM

Dear Forum.

The subject of this thread is kind of a continuation of a previous thread which had previously been marked as 'Solved'. I am therefore starting this new thread. Here is the link to the previous thread if anyone wanted some background on the topic.

http://www.excelforum.com/excel-new-...int-order.html

shg, if you get the chance, please see the attached workbook which is set up (to my understanding) as you had directed in rows 1-10. I was able to enter the integer constrant after you clarification and now the Solver tool provides results.

However it doesn't seem to be finding the least expensive option. In rows 13-16 I have set up for manual entry (user to input quantities in cells C13-15) to check the Solver's results. It seems to be ignoring the 1000 container option for producing it results as I have yet to see it populate any quantities for 1000 litre containers in it's results.

In the example shown it returns a solution of €88,999.00. User input results €85,000.00.

Any ideas on how to fix as it does indeed seem that this is the 'built in' solution for excel, and would seem to be an excellent tool when dealing with more than the 3 container sizes that my previous thread was based on.

Thank you again for your insight.

**Alf** · 01-12-2012, 08:48 AM

Cell A4 should be

=SUMPRODUCT(C7:C9,A7:A9)

Cell B4 should be

=SUMPRODUCT(C7:C9,B7:B9)

Alf

**Cidona** · 01-12-2012, 09:19 AM

Doh!!!

Good catch there Alf!!

Work great now.

Thank you very much!!1

**Alf** · 01-12-2012, 01:50 PM

Glad to be of help.

If you like to “trim” your Solver model you could click on “Options” and check the boxes marked “Assume Linear Model” and “Assume Non-Negative”.

You can then delete the constraint “C7:C9>=0”. This will not change the result nor make Solver run much faster as your model (in Solver terms) is quite small.

Perhaps you should add more constraint to make your model as close to “real life” as possible? Do you have a transportation cost depending on the size of the paint can? Is the space for storing the cans a limiting factor? Things like this could be added to your Solver model.

Alf