Solver problem

roscoe

Well-known Member
Joined
Jun 4, 2002
Messages
1,016
OK, I can't figure this out. I'm trying to use Solver to determine an input parameter of the BINOMDIST() formula that provides the exact answer I want. It works for some inputs and outputs but not for all.

For example, I want 4 successes out of 7 trials to yield a 95% answer, meaning I need to itterate on the "P" input in the formula [=BINOMDIST(4,7,"P",TRUE) should result in .95]. Using solver it iterates successfully to .3413.

Now, change 4 to 6, and solver drives "P" to an answer that causes a #NUM result (P= 4.023), however I can manually input 0.6518 and I get the right answer.

I've tried adding constraints that won't let the input go outside the bounds of 0 and 1.0 but it does anyway.

Ideas?
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
Hi Roscoe,

How about as an alternative, using the goal seek fucntion?

I tried it just now, had problems like you with the solver but success with goal seek.

HTH. Regards, Fazza
 

c_m

Well-known Member
Joined
May 29, 2008
Messages
836
Yup, same here...so how come goalseek works and solver doesnt?
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
Yup, same here...so how come goalseek works and solver doesnt?
I am not familiar enough with either to comment authoritavely: I guess the algorithms are quite different.
 

roscoe

Well-known Member
Joined
Jun 4, 2002
Messages
1,016

ADVERTISEMENT

I ended up building two versions using the same inputs. I press one macro button to use Goal seek and another to use Solver. Solver gives more accurate answers, but Goal Seek is more likely to converge. Very odd and very annoying.

I'm just glad it's not me!
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
What about using the goal seek for a first pass and then feed this - as a starting point - into the solver?

And again, it will depend on the algorithm and I guess the settings/criteria for solver. Usually for these sort of solver routines the starting point can make a big difference, and sometimes in unexpected ways...
 

roscoe

Well-known Member
Joined
Jun 4, 2002
Messages
1,016

ADVERTISEMENT

Nope. Putting in the exact correct value still results in a divergent solution. Makes absolutely no sense whatsoever.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,334
Messages
5,601,012
Members
414,421
Latest member
tonybear1994

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