Set Solver constraints to multiple data values

[DutchDude]

Hi all,

I am having a bit of a problem right now and I cannot find any solution on it.

I am looking for the perfect Marketing investment options. I set up solver and he calculates the "best solution" for me. So far so good, works perfectly fine.

However, I would like Excel to calculate the optimal marketing budget for each product only considering specific values.

In my case I am having three products with each three possible marketing budgets. Just like this:

Product A: Budget 1, Budget 2, Budget 3
Product B: Budget 4, Budget 5, Budget 6
Product C: Budget 7, Budget 8, Budget 9

As solver is overwriting any data validation, this option will not work.

Basically, I am looking for any OR option on the solver constraints, that either allows me to select a range of data values or just input multiple data values, with Solver minding those settings.

Do you guys have any idea on how to set this up? I would highly appreciate it. Thank you in advance

[Alf]

Easiest solution a workbook with 9 worksheets. On each sheet have solver set up for one case i.e Prod A Budg1 on sheet1, then ProdA Budg2 on the next sheet and so forth.

A better solution is writing a macro that can run solver in a loop changing product and budget.

If you should need help I would recomend you to upload a "sample" workbook.

Alf

[DutchDude]

Hi Alf,

thanks for your quick reply. I was also thinking of a looping macro, however I am like a beginner on that, but looking forward to test this. However, do I have to define each possibility or will the macro generate these for me? If I need to do it by hand I probably will not do it as I am having 12 products with each three possible budgets, so there would be 12^3 possibilities. I found this on the internet http://support.microsoft.com/kb/843304. Is that the right approach? Thank you again

Dylan

[Alf]

Yes that is a good start for writing macros using Solver.

To that I would add http://peltiertech.com/Excel/SolverVBA.html and perhaps this site could be worth a visit as well http://www.solver.com/.

There are also a number of posts in this forum about running solver but due to problems after a "recent" upgrade the search fuction does not work very well. Use Google instead.

Alf