In excel solver, in some situations during iterations some error values of target cell or contraints can happen such dividing by zero. what should i do in such situation to get the solution ?
In excel solver, in some situations during iterations some error values of target cell or contraints can happen such dividing by zero. what should i do in such situation to get the solution ?
You better upload an example file. Based on your information it is not possible to give any good advice.
Alf
I attached the excel file and the example i trying to solve
Most of the time when I get errors like that, I first check to make sure the spreadsheet is put together correctly and that the algorithm appears to converge correctly.
One observation, the formula for H appears to be using the group contributions for V instead of H.
Check what conditions cause the error and see if you should ever be near those conditions. For example, in this case, it appears that a divide by 0 is caused when all of the n(i)'s are 0 -- a 0 length polymer (which is a completely unreasonable condition, isn't it?). Perhaps a condition to specify a minimum polymer size, or a minimum number of methylene groups, or similar.
Another cause of errors is a poor choice of initial condition. As noted, a 0 length polymer is unreasonably, so using 0's (or anything near 0) for n(i) might be a poor choice for initial conditions.
Originally Posted by shg
The constraint for B28 does not look right to me. Formula for B28 is “=18*B25/B22” and this should be less than 0.005?
Have changed your constraint by setting
B26 <= G26
B27 <= G27
B28 >= G28
and setting B13 to 1 as an initial starting value so Solver will now find a solution.
I would recommend when setting up Solver to use “loose” constraints and as soon as a solution is found starting to tighten up the constraints.
Alf
thanks Alf and MrShorty
MrShorty your help is very useful for me as there was a mistake in calculating H i adjusted it and it returned a more reasonable solution but not the correct one
n1 to n7 must be integers
can you help me more with this ?
Assuming the newer versions of Solver have the same options available in my older version, it should be real easy to set a condition that n1:n7 = int, though, at the same time, it might be just as easy to let Solver come to its best non-integer fit, then round the results to the nearest integer.n1 to n7 must be integers
With this kind of problem (7 changeable variables) and the other complexities in this, I'm not at all surprised that there are multiple possible minima. How are you determining that the n1=2/n7=3 solution it converged on is not "the" solution?it returned a more reasonable solution but not the correct one
Assuming you are using the default Newton/Raphson method, the way you deal with multiple solutions is to start with different initial guesses. I believe your version of Solver has an optional evolutionary algorithm that may do better at locating a global minima from within the many local minima that are present.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks