Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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)? -- anb001 ------------------------------------------------------------------------ anb001's Profile: http://www.excelforum.com/member.php...fo&userid=2809 View this thread: http://www.excelforum.com/showthread...hreadid=278262 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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)? -- anb001 ------------------------------------------------------------------------ anb001's Profile: http://www.excelforum.com/member.php...fo&userid=2809 View this thread: http://www.excelforum.com/showthread...hreadid=278262 |
#4
|
|||
|
|||
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)? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I cant use englisch function names in a swedich version of excel | Excel Discussion (Misc queries) | |||
change function variable prompts?? | Excel Worksheet Functions | |||
Counting Function Dilemma | Excel Worksheet Functions | |||
need to save values from a function before it changes | Excel Worksheet Functions | |||
SUMIF(AND) FUNCTION | Excel Worksheet Functions |