Topic:Excel for Counting and Discrete Probabilities

From SharedExperienceProject

Jump to: navigation, search

Contents

Topic Highlights

(What you will learn)

  • The basics of how Microsoft Excel is used for counting problems and discrete probabilities

Introduction and Motivation

(Why learn it)

Not only is Microsoft Excel a very useful tool for statistical analysis, but it will be used extensively in the course Business Statistics I. If you have not already become familiar with it, you will need to do so.

By following the example in this topic, you will learn how to solve basic counting problems (permutations and combinations) and basic discrete probability problems (binomial and Poisson).

For an introduction to the theory behind this hands-on topic, you should refer to the following basic topics:

Learning Activities

(What I need to do to meet the objectives given below)

Learning activities for this topic
Type Name Direction
In-class activity
 Self-directed
Reading
  • For Binomial probabilities in Excel, also see Example 5.13 on page 191 of Kvanli et al.
  • For Poisson probabilities in Excel, also see Example 5.18 on page 202 of Kvanli et al.
 Self-directed
Review and reflection
  • Try your hand at Lab Assignment 1 (handed out in class)
Self-directed

Exercises

Getting Started

Open a new Excel worksheet. If you can't remember how to do this, then refer to our earlier topic on Introduction to Excel for Statistics.

Now put in an appropriate heading for each of the questions you are asked to answer. An example is shown below, although yours may look a little different from this, depending on what you are asked to do.

Don't forget to save your Excel worksheet somewhere where it can be accessed next time you need it. You might also e-mail it to yourself for safe keeping.

My First Counting Examples in Excel

Example 1 - Permutations

Now, let's assume you are given a problem that requires you to compute a permutation like 10P3. You would do this as follows:

1. Write the information that is given, i.e. n = 10 and k = 3

2. Make a spot for the required solution, i.e. nPk =

These are shown below.

3. With the cursor in the cell you wish to place the outcome, locate and press the function button as shown below:

4. Search for the permutations function by name and select the PERMUT function, as shown below.

5. Fill in the cell locations corresponding to the input values, as shown below. Press enter.

You should now see something like the following:


Example 2 - Simplifying Things

Once you get comfortable with a function, you can also type the formula directly without using the function button, as follows:

=PERMUT(10, 3)

This looks something like the following in Excel:


From here on, we'll just show the direct use of the function to save space. You can always use the interactive functionality if you wish, by pressing the function button.

Example 3 - Combinations

Combinations are computed in very much the same way as the permutations above. The only difference is that you use the following function:

=COMBIN(n, k)

For example, to find 10C2, you would type the following into the output cell:

=COMBIN(10,2)

which gives 45.

Example 3 - Products of Combinations

Just like the output of any function, you can also create the product of two outputs.

For example, the product 10C2• 7C3 can be obtained as follows:

=COMBIN(10,2)*COMBIN(7,3)

which gives 1575.

My First Binomial Probability Examples in Excel

As you know from the topic Binomial Random Variables, probability questions involving binomial random variables most often provide:

  • The number of trials, n
  • The probability associated with a successful outcome, p

and they most often ask for the probability associated with a value x, such as:

  • P(x=5) or
  • P(x<=5)

These problems are solved in Excel using the following function:

=BINOMDIST(x, n, p, cumulative)

where cumulative is either 0 or 1 to indicate whether you seek just the value P(x), or the sum of the probability values up to P(x).

Example 4

If you roll a coin three times, what is the probability of getting 2 heads?

Knowing that n=3, p=0.5 and x=2, you would solve this by typing:

=BINOMDIST(2, 3, 0.5, 0)

which gives 0.375000000.

Notice that we use cumulative = 0 because we want P(x=2) and not P(x<=2).

Example 5

For the above example, what is the probability of getting 2 or less heads?

Here, we type:

=BINOMDIST(2, 3, 0.5, 1)

which gives 0.875000000.

Notice that we use cumulative = 1 because we want P(x<=2) and not P(x=2).

My First Poisson Probability Examples in Excel

As you know from the topic The Poisson Distribution, probability questions involving binomial random variables most often provide:

  • The expected number of events, Image:Mu_for_poisson.png, in a given time period, T

and they most often ask for the probability associated with a value x, such as:

  • P(x=5) or 
  • P(x<=5)

These problems are solved in Excel using the following function:

=POISSON(x, mean, cumulative)

where mean stands for Image:Mu_for_poisson.png.

Again, cumulative is either 0 or 1 to indicate whether you seek just the value P(x), or the sum of the probability values up to P(x).

Example 6

If you are told that 7 people typically enter a restaurant in 5 minutes, then what is the probability of exactly 3 people entering in 5 minutes?

Here, Image:Mu_for_poisson.png = 7 for T = 5,

And we want: P(x=3)

So, we write:

=POISSON(3, 7, 0)

which gives 0.052129252.

Notice that we use cumulative = 0 because we want P(x=3) and not P(x<=3).

Example 7

For the same example, what is the probability that 3 or less people will enter in 5 minutes?

Here, we write:

=POISSON(3, 7, 1)

which gives 0.081765416.

Notice that we use cumulative = 1 because we want P(x<=3) and not P(x=3).

Example 8

Now, what if we wanted the probability that more than 3 people enter in 5 minutes?

Now we write:

=1-POISSON(3, 7, 1)

which gives 0.918234584.

Retrieved from "http://www.shex.org/wiki/Topic:Excel_for_Counting_and_Discrete_Probabilities"
Personal tools