# 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

**=1-p (Schema Bernoulli).**

*q*Define ** x** as a test number, which was recorded

**success. In this case, the random variable**

*first***will have a**

*x**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)

## Tasks

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?

**Решение1**:

=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 *Lada**Granta*. 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 *L*** 28** Selection, you need to ensure that the result of the formula was 0.5 (approximately).

## distribution Options

the example file, on sheet Example there are formulas to calculate mathematical expectation = (1-p)/p variance (square of standard deviation) =(1-p)/p^2.

**TIP**: Other distributions in MS EXCEL can be found in the article Distribution of a random variable in MS EXCEL.