Solver not meeting constraints when it claims to do so

Hi,

I am using Excel's solver to solve the equations of motion for a two degree of freedom system on a row-wise basis using a simple VBA loop. I've done this several times for other models and never run into this problem before.

The problem is that the Solver claims to have found a solution for each row that satisfies the objective and the single constraint. However, it hasn't.

The two equations of motion are written so that they should equal 0 at every time step so the objective and constraint are similar: set a cell value equal to 0.

The Solver options are default: precision is 0.000001, convergence is 0.0001 and the engine is GRG Nonlinear.

Some rows solve perfectly, with the cell values stopping at very small numbers - typically with exponents of -5 to -10. Often, however, they stop with values that are 0.X or 0.0X, which clearly does not satisfy the required precision.

Pausing at each iteration shows that only 2-3 iterations are being used before it claims to have found a solution. Setting the max time to 20 (seconds?) and iterations to 999 has no effect.

Here's the weird thing: I tend to set the two variables that it is adjusting to 0 before each run whilst I investigate the problem. However, if I run the loop to get a first set of answers (many of which are poor as described above) and then run the loop again using the first results as start points then it finds good solutions!

To try and combat this, I put in a few extra lines of code that copy the result from the current line into the next line so that each line's start point is not 0 but the same as the result from the line before. This didn't work either and still requires a second run-through. I then thought about using an extrapolated value based on the previous two results but then thought I'm slowly writing my own solver!

I have tinkered with every solver option but to no avail. The main problem for my is why is it ignoring the requested precision?

I also tried setting upper and lower values instead of 'equal to 0' but it ignored those constraints as well. I can't interpret the reports unfortunately but did notice that when a constraint was met it was labelled as non-binding and when not met it was labelled binding. Could that be a clue?

I'd be grateful for any help.

Thanks,

Simon.

Perhaps you could upload the file with your solver model (removing sensitive first) so that forum members may have a go at trying to solve this problem.

Alf

Hi Alf,

I don't seem to be able to add attachments unfortunately. If I press the attachments button then I get a small blank pop up and the words (in Chrome, anyway) JavaScript voids: attachments.

I've tried IE and the same thing happens.

Sorry.

Go Advanced -> Manage Attachments -> Upload

And where is Advanced??

Button next to Post Quick Reply

I've uploaded the file but can't see any evidence of that in this thread. I'll post this message and see if it works.

Simon.

Perhaps the file was too large so I'll try again with a reduced version.

If you press the button on the sheet it will run a simple solver loop over a few of the rows, some of which are known to produce a result that does not meet the constraints.

Thanks,

Simon.

Thanks for your work Alf.

I managed to find and solve the problem - you touched upon it when referring to decimal places. To diagnose the problem I ran the macro once on a dozen rows or so and then only ran it for a second time on a single row. This ruled out influence between rows and showed me that the difference between the results from each of the two runs were solution values of XE-11!

I initially considered changing the parameter units to use millimetres instead of metres and millrad instead of rad to avoid such small numbers but before that I just set the precision to 1E-13. That worked perfectly and hasn't slowed the solver to any significant degree.

So, in conclusion, I think it is misleading for the solver help pages to state that the precision controls the degree to which the constraint is satisfied as that clearly isn't true. However, the precision does control the degree to which the variable cells are changed in order to satisfy the constraints.

Regards,

Simon.

Hi Simon
You are welcome and thanks for feedback.

So the precision controls the degree to which the variable cells are changed in order to satisfy the constraints as long as the constraints are greater than the smallest number that the computer will work with?

Good to know as well as knowing that you have solved your problem.

Alf