How to increase the trials for solver in data tab?

Mosen87

New Member
Joined
Mar 9, 2020
Messages
36
Hello
I have a target function changing with two other variables. I want the target function be zero by changing the two variables but I don't get the right solution. Is there a way I can increase the trails Excel does to find the solution? I see at the bottom right of excel, It counts the trials up to 21 in my case.
Secondly, In case It cannot find any solution, Is there a way to make the target function a number close to zero not exactly zero, so It might help find a solution.
Thanks
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,708
Do you mean Iterations?

1605225155247.png
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,739
Can you show a sample of your sheet, with the function you're trying to set to 0, and the starting values? It would also help if you could show how you set up the Solver, with the parameters, constraints and solving method.
 

Mosen87

New Member
Joined
Mar 9, 2020
Messages
36
Can you show a sample of your sheet, with the function you're trying to set to 0, and the starting values? It would also help if you could show how you set up the Solver, with the parameters, constraints and solving method.
The target function is a bit complex. If you see the image, It's the Opt Func with its value in front.
Thanks
 

Mosen87

New Member
Joined
Mar 9, 2020
Messages
36

ADVERTISEMENT

If you see row 66, the first one is 0.0 means the solver could solve it but the one next to it not which means the solver couldn't solve it.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,739
In your example, the Solver found the answer in column B (result = 0) after only 5 iterations, so it stopped. It did not need more. I assume that in column C, Solver did not find a solution but ended after only a few iterations. Since you did not provide the function, or the Solver settings, I'm afraid I don't have much to work with. If it ends so quickly, my best guess is that your function is non-continuous. Some of the Solver methods only work on continuous functions. Try again using each of the different Solving Methods. Evolutionary is probably the one that would work best, but it still strongly depends on your function.

As far as getting a "close" answer instead of an exact one, if an exact one doesn't exist, it depends on your function again. If the result of the function in B66 is always non-negative, you can just change the Solver settings to "minimize the value" instead of setting it to 0. If the function can be negative, wrap it in an ABS function, then choose "minimize the value".

I don't know what your function is, but sometimes it's possible to rewrite it so that it behaves better for Solver, but that's probably up to you.
 

Mosen87

New Member
Joined
Mar 9, 2020
Messages
36
In your example, the Solver found the answer in column B (result = 0) after only 5 iterations, so it stopped. It did not need more. I assume that in column C, Solver did not find a solution but ended after only a few iterations. Since you did not provide the function, or the Solver settings, I'm afraid I don't have much to work with. If it ends so quickly, my best guess is that your function is non-continuous. Some of the Solver methods only work on continuous functions. Try again using each of the different Solving Methods. Evolutionary is probably the one that would work best, but it still strongly depends on your function.

As far as getting a "close" answer instead of an exact one, if an exact one doesn't exist, it depends on your function again. If the result of the function in B66 is always non-negative, you can just change the Solver settings to "minimize the value" instead of setting it to 0. If the function can be negative, wrap it in an ABS function, then choose "minimize the value".

I don't know what your function is, but sometimes it's possible to rewrite it so that it behaves better for Solver, but that's probably up to you.
Please see the file I attach here. You can see the B65 and C65 are target functions and B8, B9 and C8 and C9 are starting values.
1) "Solver did not find a solution but ended after only a few iterations", Why not continuing for more iteration?
2) "minimize the value" instead of setting it to 0. I like the idea. Mine is positive. I don't the columns by hand. Instead I have a Macro code that goes for all columns one by on. How can i do what you said?

1605294108556.png

1605294169037.png

Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,126,922
Messages
5,621,618
Members
415,847
Latest member
AlpinoHirsch

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top