My Math Forum Calculating Hypergeometric Distribution in Excel

 Algebra Pre-Algebra and Basic Algebra Math Forum

 March 2nd, 2014, 03:59 PM #1 Senior Member   Joined: Jan 2014 Posts: 196 Thanks: 3 Calculating Hypergeometric Distribution in Excel I attempting to use the hypergeometric distribution calculator in excel for the start of a problem, and I am trying to figure out my issue. This problem should have a prob/result of $> 0$. The result is  Not sure if it makes a difference, but this is excel 2007 for Mac. for sample_s 1 number_sample 12006 population_s 12000 number_pop 3,000,000 The problem is asking about multiple lottery drawings, and this part is for a drawing that pays \$25 for 12000 of the 12006 tickets drawn out of 3,000,000. So I am transferring the data to the calculator from $\frac{(_1^{12000})(_{12005}^{2988000})}{(_{12006}^ {3000000})}$ The rest of the problem will apply to the other five tickets, this is just the first part. Thanks for any help!
 March 4th, 2014, 10:28 AM #2 Senior Member   Joined: Jan 2014 Posts: 196 Thanks: 3 Re: Calculating Hypergeometric Distribution in Excel Could it be a rounding issue, or am I not setting this up correctly? Thanks for any help!
 March 4th, 2014, 10:37 PM #3 Senior Member     Joined: Dec 2013 From: some subspace Posts: 212 Thanks: 72 Math Focus: real analysis, vector analysis, numerical analysis, discrete mathematics Re: Calculating Hypergeometric Distribution in Excel I'd think that the reason is the limited accuracy of floating point numbers in Excel. If I can remember right (I don't have Excel anymore), the accuracy limit is about $10^{\pm300}$. Now, you are trying to calculate numbers like $\binom{3000000}{12006} \approx 2.067830012879723414612416405084105771934086242732 21951... \cdot 10^{33988}$ that are far away. The trick you can do, is set your computing so that in every step you have enough accuracy. For example, your problem can be written as $12\,000\cdot 12\,006\cdot\frac{2\,975\,996\cdot \ldots \cdot 2\,988\,000}{2\,987995\cdot \ldots \cdot 3\,000\,000}.$ If you now split this up and compute parts of the product like this $12\,000\cdot 12\,006\cdot\frac{2\,975\,996}{2\,987\,995}\cdot \ldots \cdot \frac{2\,988\,000}{2\,999\,999}\cdot\frac{1}{3\,00 0\,000}$ you should be able to get the result. This kind of tricks are usual when dealing with very large or tiny numbers. Also, you can use logarithms: The logarithm of product is the sum of logarithms of individual terms, so you would end up with a sum of logarithms. If you compute the sum somehow (Excel, calculator, etc.) you can calculate the final result. I've usually used this logarithm approach since it is quite general and straightforward to code.

### how to binomial distribution probability excel my math lab

Click on a term to search for related topics.
 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post WWRtelescoping Algebra 2 March 2nd, 2014 09:08 AM Anton29 Advanced Statistics 0 June 15th, 2012 03:59 AM Anton29 Advanced Statistics 0 June 7th, 2012 02:36 AM hollymxox Probability and Statistics 1 April 19th, 2012 05:21 PM looser Advanced Statistics 3 December 1st, 2010 05:36 AM

 Contact - Home - Forums - Cryptocurrency Forum - Top