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?

 

[roscoe]

I was forced to try this again...and the problem still exists. I'm hoping somebody will have an idea. I have a ton of test data coming up and I really need a reliable way to solve for the BINOMDIST when I know the answer and am looking for one of the inputs.

Neither solver nor goal seek reliably find the answers, and I can't constantly be rewriting macros...

Ideas?

 

[Fazza]

Can you write a custom function to do what you need?

 

[roscoe]

I wouldn't know where to start, but that's exactly what I need. Problem is that solver as I've used it uses cells as inputs...no clue how to do that in a custom function

 

[Fazza]

I see there is another thread that seems to duplicate this same question... and that other thread has a solution... I'll assume this thread I'm in should be closed and future work should be in thread http://www.mrexcel.com/forum/showthread.php?t=427857

 

[roscoe]

Yes, I got my answer as posted in the other thread.

 

[Fazza]

Yes, I got my answer as posted in the other thread.

I'm happy that you sorted it out, well done.

Please note that duplicate threads - that don't state such - are against forum rules (generally, I haven't checked this particular forum). I could well have taken some time to have written a custom function to answer this thread in ignorance of the solution in the other thread. That is one reason to not have duplicate threads. cheers

 

[roscoe]

The other post was months old...I had forgotten about it.

 

[Fazza]