Solver Problem ( related to earlier post of using if an Vlookup)

  • Thread starter Thread starter Honey
  • Start date Start date
H

Honey

Sorry I have so many threads going at the moment but I thought it
would be easier to consolidate them in one.

This is my Problem

I have a exercise sheet - I want to be able to enter the amount of
calories I need to burn and solver to find a reasonable solution to do
it an set amount of time.

I have related each exercise to a table so that solver can change a
column of numbers to between 1 and 4 so you get a reasonable amount of
time . ie swimming:

0 0
1 15 ( minutes)
2 20
3 30
4 45

I have a column D10 where you can put 0 if you dont want to include
that exercise and 1 if you do

I then have related this to a the tables with this
formula :=IF(D10=1,VLOOKUP(C10,M$15:N$19,2),"0") and this works fine
manually.

When I run solver...changing the column of numbers between 1-4 it wont
find any solutions.....is this because nesting if and vlookup is too
much for solver?

Hope this is clear I have confused the hell out myself and the laptop
is getting closer and closer to the window.
Regars,
Lyndsey
 
...is this because nesting if and vlookup is too
much for solver?
Click to expand...

Hi. In general, yes. Excel's Solver does not work with functions like "If"
and "VLookup."
Solver can't keep track of "why" the Target value "Jumped" with a small
input change.
...D10 where you can put 0 if you dont want to include
that exercise and 1 if you do.
Click to expand...

In general, you can have Solver do this by adding the constraint that D10 is
"Bin" (meaning Binary). This limits D10 to 0/1.

If you have any questions, feel free to send me a small example file.
 
Back
Top