Excel solver problems
Excel solver problems
(OP)
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.
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.





RE: Excel solver problems
RE: Excel solver problems
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?
RE: Excel solver problems
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: Excel solver problems
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
RE: Excel solver problems
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.
RE: Excel solver problems
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.
RE: Excel solver problems
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.