Solver/Goal Seek HARD Optimization Issue

Thanks for taking the time to help - I will lay out what my data looks like, what I am trying to do, and what I have attempted already - The short and sweet of it, however, is that I am looking to use some type of optimization feature. I have also included a sample file of my problem!!

WHAT MY DATA LOOKS LIKE:
I am analyzing food as nutrition compares to user enjoyment -- I have a list of different types of food in Column A, in Column B is a numerical score that I have given the food based on nutrition (Nutrition Rating). In Column C I have a numeric score given to each food based on how much people enjoy eating it (Enjoyment Rating).

WHAT I AM TRYING TO DO:
I would like to run an optimization analysis to find how to maximize the Enjoyment Rating given that I constrain the number of food options (say there can only be 5 foods) and that I constrain the Nutrition Rating (nutrition rating cannot exceed 100).

THE TROUBLE AND WHAT I HAVE DONE:
I have not been able to find a way to optimize while keeping the nutrition value of each food the same and selecting the best combination of 5 foods from a list of 100 foods. I have attempted to use Goal Seek and the add-in Solver, however, I am having trouble setting this up to use the list of foods and keep the nutrition value the same in order to maximize the sum of Enjoyment Rating which also stays the same. In other words, I do not know what to use as the variable that goal seek and/or Solver changes.

All help is extremely appreciated and welcome!

Thanks!

Have set up a possible model for you.

First Data for solver to work on must be on the "Active Sheet" i.e. the same sheet as you will run solver on. This is why this model it set up on the data sheet.

In you spcification you state that the number of foods picked must be 5 but you also require the solution to contain 2 sides, 3 fruits and 1 entree and that adds up to 6???

Alf

Alf,
Thank you for response. I will first respond to your comments and then add additional info on problem. Attached is the updated Excel document to reflect changes.

YOUR RESPONSE:
You are correct to point out the discrepancy between 5 and 6 -- It should add up to 5. I corrected this on the attached Excel.
Your solution is neat and smart with the Sumproduct function, and this solves 90% of the problem. I did forget, however, to include one additional constraint.

ADDITIONAL PROBLEM:
I forgot to add a constraint on Nutritional Rating - The sum of nutritional rating cannot exceed 20. I am not very familiar with solver tool. It would be extremely helpful if you could briefly detail what steps I would take to go about executing this in Solver.

The attached spreadsheet accurately reflects all constraints on the "Optimize" sheet (I understand that this cannot be used for the solver itself however).

Thanks Alf!

ALF,
I just played around with solver and was able to add the constraint of the the Nutritional Value to be <= 20 and saw your Binary constraint that made this all work.

Thank your for help!

I spoke too soon -

Upon going back over data there is still a glaring issue -- the binary columns on the right with Side, Fruit, and Entree, do not actually honor the category of food -- so the solver finds an optimal solution, but it does not actually give 2 sides, 2 fruits and 2 entrees. A cheap way to fix this that I found is to sort the data based on the food category and only have the binary columns in accordance with where the category is. Refer to attached Excel sheet to understand what I am saying -- I am looking for a smarter way to do this though.

All help is celebrated.

I am not very familiar with solver tool.

Then I’ll try to give you a quick rundown on Solver, the how and the why.

Solver in Excel 2010 (probably 2013 as well) comes in 3 flavours

The “Simplex LP”, the “GRG nonlinear” and the “Evolutionary”

The “Simplex” requires the problem to be linear i.e. “y = k + a*x” is a typical linear function.

The “GRG” for non linear problem i.e. curves i.e. “y = x^2 + bx “ par example.

The “Evolutionary” for problems that are “non-smooth” (don’t ask me to explain I just quote the manual).

How does one know which model to pick? I start by testing the “Simplex” if the problem is not linear Solver will immediately tell me so. Then I try the “GRG” to see if this works and finally the “Evolutionary”

Even if the models are different they all have this in common:

You define a target cell, there you can select min, max or a specific value.

You tell Solver what cells it can change to reach the min, max or specific value. You must ensure that a change in the “cells to change region” changes the value in the target cell.

Then you use constraints to tell Solver the rules for changing the cells. You can have “Integers”, “Binaries”, equal or less than a specific value, equal to a specific value or equal or greater than a specific value and different i.e. you specify a range as different and none of the cells to change can have the same value an another in that specified range.

The Excel solver has a severe limitation in the number of constraints it can handle, as the makers of solver would like to sell you their more powerful model for about 3500 $.

Let me now have a look at you model and see if I can come up with anything useful.

Alf

Ok I fixed it, don't know what happened to my Norwegian brain!

I've included anoter way to set up solver not that different but have a look at it and see if this is easier, simpler??

Alf

Alf,
Thank you for taking time to write a thorough break down of solver and edit the document -- I was playing around with the sheet as well and found yet another alternative solution that was inspired by your sumproduct table -- I will attach here for reference and change to "SOLVED"

Thanks for your help.

You are welcome.

Thanks for feedback and rep.

Alf