Solver problem

[roscoe]

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?

 

[Fazza]

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]

Yup, same here...so how come goalseek works and solver doesnt?

 

[Fazza]

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]

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]

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]

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

 

[Fazza]

Well, it is often the way with this sort of thing.

 

[roscoe]

Thanks anyway.

 

[Fazza]