HYPGEOM.DIST Excel

What Is HYPGEOM.DIST Excel Function?

The HYPGEOM.DIST Excel function is a statistical function that calculates the probability of a specific number of successes in a sample drawn without replacement from a given population. It is commonly used in hypothesis testing and analyzing experiment results.

In the following example, let us look at the HYPGEOM.DIST Excel function and how it works. Apply the below formula in cell A2 where the different arguments denote the number of successes and size of the success and population, respectively, and the TRUE indicates the cumulative distribution function.

=HYPGEOM.DIST(1,20,40,100,TRUE)

Press Enter to get the result.

What Is HYPGEOM.DIST Excel Function?

The HYPGEOM.DIST Excel function calculates the probability of getting a certain number of successes in a sample taken from a larger population without replacement.
It is used in hypothesis testing and analysing experiment results.
This function requires four inputs: the number of successes in the sample, the size of the sample, the total number of successes in the population, and the population size.
The HYPGEOM.DIST Excel function is best suited for scenarios where the sample size is relatively small and changes the probability distribution as individual items are selected.

Syntax

Sample_s – (Required)The variable represents the count of favorable outcomes observed within the provided sample.

Number_sample – – (Required)It is the sample size.

Population_s – – (Required)The cumulative count of successful outcomes within the entire population.

Number_pop – – (Required)It is the population size.

Cumulative – – (Required)The logical value in question determines the format of the function. When set to TRUE, the HYPGEOM.DIST function will provide the cumulative distribution function when set to FALSE, the HYPGEOM.DIST function will provide the probability mass function.

How To Use HYPGEOM.DIST Function in Excel?

To effectively utilize the HYPGEOM.DIST function in Excel, follow these steps.

#1 – Access From the Excel Ribbon

Step 1: To designate the cell where the result will be displayed, choose an empty cell. Then, head over to the “Formulas” tab and click it.

Step 2: To access more features, just go to the menu and select the “More Functions” option.

Step 3: To access the Statistical option, you can easily select it from the drop-down list. Afterward, proceed to the drop-down menu and choose HYPGEOM.DIST.

Step 4: A window called “Function Arguments” will appear. Please enter the values for the number of arguments in the “sampl_s,” “number_sample,” “population_s,” “number_pop” and “cumulative” fields. Once you have completed this step, click on the “OK” button to proceed.

#2 – Enter the Worksheet Manually

Step 1: To designate an empty cell for the output, simply enter “=HYPGEOM.DIST()” in the desired cell. Alternatively, you can type “=H” and then double-click on the “HYPGEOM.DIST” function from the list of suggestions provided by Excel.

Step 2: To obtain the desired outcome, kindly press the “Enter” key.

Examples

Example #1 – Probability Density Function

Let us look at how to use the HYPGEOM.DIST function to calculate the sample hypergeometric distribution value of the probability density function.

In our example, say we have a collection of delicious M&Ms and Skittles in a bag. There is a total of 10 each of these two chocolates in the bag. Suppose you are a fan of Skittles and want to choose precisely three Skittles packs from the bag when you draw out candy for five trials. The possibility of this occurrence can be calculated as follows. Look at the below table.

Here, we have sample_s at three since we want to draw precisely 3 Skittles, while the total number of trials is 5. The number of Skittles in the total population is ten, while the size of the population is 20. Since we are calculating the probability density function, the fifth argument is FALSE.

Step 1: Select cell B6 and enter the HYPGEOM.DIST formula in the designated cell:

=HYPGEOM.DIST(B1,B2,B3,B4,B5)

Step 2: Press Enter. The outcome is showcased in cell B6.

By following these steps, you can observe that the PDF for this occurrence is 35%.

Example #2 – Cumulative Distribution Function

To understand the functionality of the HYPGEOM.DIST Excel function further. Let us look at the example below. The function calculates the sample hypergeometric distribution value of the cumulative distribution function for different sample values.

In a bag containing 12 red and 43 green balls, the possibility of getting utmost none, 1, 2,3, 4, or 5 red balls in a sample trial of 10 is shown below. A graph depicting how this possibility varies is also shown. Below, we have listed the different samples and other parameters in a table.

To optimize the calculation of the HYPGEOM.DIST Excel function, follow the steps outlined below:

Step 1: Select cell B6. Enter the following HYPGEOM.DIST formula here:

=HYPGEOM.DIST(B1, B2, B3, B4, TRUE)

Step 2: Press Enter; you get the possibility of no red balls occurring in a trial of 10.

Step 3: To find the possibilities of the utmost occurrence of other samples, copy-paste the formula in cells C6 to G6, changing their parameters as required. You get the CDF values for each of the samples, which are plotted in the graph below.

Example #3

Now let us look at an example with both the Probability Density Function and Cumulative Distribution Function being calculated using the HYPGEOM.DIST Excel function.

Consider a factory producing some items. When you sample 20 items randomly, what is the probability that exactly 5 of them are defective or up to 2 of them are defective.

For the former, we must find the probability density where the cumulative parameter is FALSE. For the latter, we find the CDF where the cumulative parameter is TRUE.

In the table below,

The sample_s value in the first and second scenarios is 5 and 2, respectively.

The number of draws is 20.

The total number of defective pieces is 10.

The number of items in the population is 100.

To understand the efficiency of the HYPGEOM.DIST Excel function calculation, execute the following steps:

Step 1: Input the entire HYPGEOM.DIST formula into the designated cells, B6 and C6, as follows:

=HYPGEOM.DIST(B1, B2, B3, B4, FALSE) for PDF

=HYPGEOM.DIST(B1, B2, B3, B4, TRUE) for CDF

Step 2: The outcomes will be showcased in cells B6 to C6, accompanied by a visual graph illustrating the difference between the population density function and the cumulative distribution function, just like the image given below.

Important Things To Note

The #NUM! error occurs if the entered value of:
- sample_s is < 0 or > number_sample;sample_s > population_s;sample_s > (number_sample – number_pop + population_s)number_sample ≤ 0 or > number_pop;population_s ≤ 0 or > number_pop;
- number_pop ≤ 0.
The #VALUE! error occurs if any argument entered is non-numeric.
It is commonly used to model situations in statistical analysis that involve finite populations, such as sampling without replacement.

Frequently Asked Questions (FAQs)

What does the HYPGEOM.DIST Excel function do?

The HYPGEOM.DIST function in Excel is a statistical function that calculates the probability of drawing a certain number of successes in a sample, given the size of the population and the number of successes in the entire population. The HYPGEOM.DIST function allows professionals to easily calculate probabilities related to finite populations and based on specific conditions, enabling them to make informed decisions or analyze data with ease.

How to detect errors while using the HYPGEOM.DIST Excel function?

Let us look at an example to understand the errors that can pop up when using the HYPGEOM.DIST Excel function. The table has a list of parameters to be used in the HYPGEOM.DIST formula.

Type the below formula in cells B6 & C6:

=HYPGEOM.DIST(B1,B2,B3,B4,TRUE) in cell B6 =HYPGEOM.DIST(C1,C2,C3,C4,FALSE) in cell C6

We get a #NUM! Error because the supplied population > the number_pop value.

We get a #VALUE error because the number_sample value is non-numeric.

What are the limitations of using the HYPGEOM.DIST function in Excel?

The limitations of using the HYPGEOM.DIST Excel function are;

• The function returns every outcome in a sample space, which may not be true in many real-world scenarios.
• It can only handle discrete random variables and cannot be used for continuous distributions.
• The function requires precise input parameters such as the number of successes in the population and sample size, which can be challenging to obtain accurately in practice.
• This function does not allow for complicated non-linear relationships between variables or incorporate other relevant factors that may affect the distribution.

What is the difference between HYPGEOM.DIST and BINOM.DIST functions in Excel?

The main difference between the HYPGEOM.DIST and BINOM.DIST functions in Excel lie in their underlying applications.

• The HYPGEOM.DIST function calculates the probability of obtaining a certain number of successes within a specific sample drawn without replacement from a finite population.
• Whereas the BINOM.DIST function calculates the probability of obtaining a particular number of successes in a fixed number of independent trials with a constant success rate.

Download Template

This article must help us understand the HYPGEOM.DIST Excel Function’s formula and examples. You can download the template here to use it instantly.

Hypgeom.dist Excel Download

HYPGEOM.DIST Excel