Re 'BINOMDIST' function

[anb001]

When creating the below function, I get a [#NUM!] error:

=BINOMDIST(564;1757;0.33333;TRUE)

But when I change the 'trials' number (1757), to something closer t
'1000', e.g. '1010', then it returns a value, as it should.

How can this be (I'm using Excel XP)?
:confused

 

[Alex Delamain]

I am guessing that you have just come up against a limit in excel'
capability. If you reduce the number of successes or the number o
trials you can increase the value of he other. Using office 2002 an
your figures I hit the #num limit at 1029 trials so it is not just X

 

[Dana DeLouis]

In Excel 2003, I get the following

=BINOMDIST(564,1757,0.33333,TRUE)

returns:
0.142006496

I think this was one of the fixes they did to the statistical functions.
See Microsoft's kb article number 827459

(Microsoft's support site is down right now...It is requiring a Passport to
get to their site, and locks up Internet explorer :>0 )

 

[Jerry W. Lewis]

If you are not prepared to upgrade to Excel 2003, and don't mind a VBA
solution, use the cdf_binomial() function from Ian Smith's probability
library
http://members.aol.com/iandjmsmith/examples.xls

Alternately, the following formulas are mathematically equivalent to
=BinomDist(x,n,p,T), and will work for your values.

=1-BetaDist(p,x+1,n-x)
=BetaDist(1-p,n-x,x+1)
=FDist(p/(1-p)*(n-x)/(x+1),2*(x+1),2*(n-x))
=1-FDist((1/p-1)*(x+1)/(n-x),2*(n-x),2*(x+1))

Note that Excel's continuous distributions only give 5-6 figure
accuracy. To 15 digits. the correct value is 0.142006496002267, which
is what Smith's function returns.

Jerry