Geometric Distribution. Discrete Probability Distributions in EXCEL
Consider the Geometric distribution, we calculate its expectation and variance. Using function MS EXCEL URBINA.DIS() construct graphs of the distribution function and probability density.
Geometric distribution (eng. the Geometric distribution) is a special case of Negative Binomial distribution(if r=1).
Let tests, each of which can only occur in the event of "success" with probability p event or "failure" with probability q=1-p (Schema Bernoulli).
Define x as a test number, which was recorded first success. In this case, the random variable x will have a Geometric distribution:
TIP: read more about distribution Function and probability Density see article distribution Function and the probability density in MS EXCEL.
Geometric distribution in MS EXCEL
In MS EXCEL, beginning with version 2010, Negative Binomial distribution feature is URBINA.DIS(), the English name NEGBINOM.DIST(), which allows to calculate the probability of occurrence of number of failures to obtain a given number of success for a given probability of success.
For Geometric distribution the second argument to this function must be 1, since we are only interested in the first success.
This definition differs slightly from the formulation given above, where the calculated probability that the first success will occur after the x test. The difference between the range of variation of the range x: if the probability is defined through a number of tests, the x can take values starting from 1, and if after the number of mishaps, starting from 0. Therefore, a fair formula: p(x_failures)= p(x_test-1). Cm. the sample file sheet Example, which shows the 2 ways of calculation.
Below, we use the approach adopted in functions MS EXCEL: using the number of failures.
to calculate the probability density function p(x), see formula above, it is necessary to set the fourth argument in the function URBINA.DIS() set to FALSE. To compute a cumulative distribution function, you must set the fourth argument equal to TRUE.
note: To MS EXCEL 2010 in EXCEL, the function ORBINARY(), which allows to calculate only the probability density. In example file a formula based on the ORBINARY() to compute the cumulative distribution function. There's also a formula to calculate the probability using definition.
example file graphs probability density function and cumulative distribution function.
note: To build a cumulative distribution function perfect graph Graph, density – column group. Read more about the build see Basic charts.
note: For ease of writing the formulas for the parameter p in the example file created Name.
note: URBINA.DIS() with a non-integer value of x, fractional part is discarded. For example, the following formula will return the same value:
URBINA.DIS(2, 1, 0,4, TRUE)=
URBINA.DIS(2,9, 1, 0,4, TRUE)
the Solution of tasks given in the the example file, on sheet Example.
Задача1. Oil company drills wells for oil production. The probability of finding oil in the well is 20%.
what is the probability that the first oil will be received in a third attempt?
What is the probability of detection of the first oil will take three attempts?
=URBINA.DIS(3-1, 1, 0,2, FALSE)
=URBINA.DIS(3-1, 1, 0,2, TRUE)
Задача2. The rating Agency is doing a survey of passers-by in the city of the beloved car brand. Let them know that 1% of the city's favorite car is LadaGranta. What is the probability that to celebrate the first admirer of this brand of car after interviewing 10 people?
Решение2: =URBINA.DIS(10-1, 1, 0,01, TRUE)=9,56%
Задача3. The rating Agency is doing a survey of passers-by in the city of the beloved car brand. Let them know that 1% of the city's favorite car is Lada Granta. How many people you need to talk to before the probability that to meet the admirer of this brand of car was 50%?
Решение3: =URBINA.DIS(L28-1, 1, 0,01, TRUE)
Changing the value of the cell L28 Selection, you need to ensure that the result of the formula was 0.5 (approximately).
TIP: Other distributions in MS EXCEL can be found in the article Distribution of a random variable in MS EXCEL.