Excel solver problems

[reeee]

Hi, I am trying to use Excel solver to give me the minimum possible number for a problem that I have, the number selected in the jpeg attached are the ones that solver has been told to change so as to get the number shown as orange to it's lowest possible point.

This number is worked out from a number of cost functions and such, the only problem is that solver doesn't seem to listen to any of my constraints so even when set to integer it still kicks out a real number... I also have to prevent solver from going too high with the numbers as this will result in an invalid number, I have tried to get solver to check the number below the ones it is changing but it ignores this constraint so I asssumed it could only handle constraints to do with the actual numbers it is changing. I have told solver to do this but yet again it doesn't listen.

This is very annoying due to the fact that I will have to wright my own macro to get the correct number and was hoping to save time by getting solver to work this out for me.

Hope this all makes sense and thanks in advance for the reply.

 

[cummings54]

You haven't given us much to go on. Solver does work. You can constrain the changing cells to integers. Perhaps testing solver with a simpler problem might give you insight.

 

[reeee]

sorry, It is a tad hard to explain really.

I know that solver works as i have used it on other calculations but I just can't figure out why it won't listen to the constraints.

in the row's selected in the jpeg, if I tell solver that the each value it chooses cannot be greater than 160 minus the sum of the previouse values It has changed it just seems to go ahead and choose a greater value anyway.

If I tell it that the rows below (these are calculated from the numbers solver chooses) it won't work, now I can understand if perhaps solver cannot be constrained by choices it hasn't made yet but then how can I possibly get solver to work out the answer? I need to get it to choose a vlaue but this value has to be greater than zero and smaller than a number that is constently changing based on solvers desicions. is this possible?

 

[IRstuff]

If you really want help, post the actual file, not a picture of a file.

TTFN

FAQ731-376

 

[reeee]

OK, I have uploaded and example of whats going on, the row highlighted in red is what I want solver to change. The row in green is what must be kept above 45 otherwise the spreadsheet will kick out an error.

The numbers solver chooses cannot be less than 0 otherwise the answer is wrong

Hope this helps, Im sorry but it's all rather confusing. i have written my own macro to get close to the minimum but I want to know how close it is.

Thanks

 

[cummings54]

With solver you can constrain a calculated cell. This makes the problem more complex. You run a risk of specifying a problem with no solution.

The workbook you posted had no solver problem so I can't comment. Try checking the "show iteration results" box in the options dialog. Seeing the progress of the calculation may help to determine if the problem is properly specified.

 

[reeee]

Sorry about all this but I am new to Solver and it just doesn't make any sense, Im actually starting to wonder if my pc is broken. I have basically set up solver to do what I would like it to.

In this example it should change the number to get the total cost to the minimum, it has to be greater than zero yet smaller than 160

Solver also has to check to make sure that row 70 does not fall below 45

As you can see in the example solver has not inputed integers, it has gone below 45 but at least it stays within the 0-160 margin

right at the bottum is a row of my calculations I get from a macro I wrote yesterday to work out the problem. it's only 50 itterations in so will get lower with time.

 

[reeee]

Managed to find out what the problem was, basically solver what calculating to a point where it made the total an illegal value i.e a minus number squared so I just added an IF statement that converted the cell to an obsene number basically telling solver to turn back and try another way.

I get a nice number then run my macro to shave off a few extra digets and sorted, the answer I was looking for!

I do appologise for being so rubbish with solver everyone but thanks for trying to help.