Re 'BINOMDIST' function
 

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 to
'1000', e.g. '1010', then it returns a value, as it should.

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


--
anb001
------------------------------------------------------------------------
anb001's Profile: http://www.excelforum.com/member.php...fo&userid=2809
View this thread: http://www.excelforum.com/showthread...hreadid=278262

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


--
Alex Delamain
------------------------------------------------------------------------
Alex Delamain's Profile: http://www.excelforum.com/member.php...o&userid=11273
View this thread: http://www.excelforum.com/showthread...hreadid=278262

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 )

--
Dana DeLouis
Win XP & Office 2003


"anb001" wrote in message
...

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 to
'1000', e.g. '1010', then it returns a value, as it should.

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


--
anb001
------------------------------------------------------------------------
anb001's Profile:
http://www.excelforum.com/member.php...fo&userid=2809
View this thread: http://www.excelforum.com/showthread...hreadid=278262

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

anb001 wrote:

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 to
'1000', e.g. '1010', then it returns a value, as it should.

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