Excel and Binomial Probability Distribution
by Vanessa Lacen (UoPhx 2008)
Probability distribution computations
are used on a large scale to determine possible outcomes in business,
medical and personal situations. The probability distributions will
assess success or failure. The findings (success or failure) are based
on the information one is searching for and not good or bad outcomes
(Lind et al., pp 188). This report will explain how to input binomial
probability distributions in Excel 2007 in order to complete the
computations. Excel is a Microsoft Office spreadsheet program created
by the Microsoft Office Corporation.
The binomial probability distribution is
a discrete probability distribution. Binomial probability distributions
have the following characteristics:
1.
An outcome on each trial of an experiment is classified into one of
two mutually exclusive categories—a success or a failure. Mutually
exclusive means if an item is in one category, it cannot be in the
other. For example, one could not be male and female at the same
time (Lind, pp 189).
2.
The random variable counts the number of successes in a fixed number
of trials (Lind, pp 189).
3.
The probability of success and failure stay the same for each trial
(Lind, pp189).
4.
The trials are independent, meaning that the outcome of one trial
does not affect the outcome of any other trial. (Lind et al.,
pp189).
Computations for the binomial probability distribution are found by the
following formula in the book Statistical Techniques in Business and
Economics, 2004, page 189.
Where:
C denotes a combination.
n is the number of trials.
x is the random variable defined as the number of successes
π is the probability of a success on each trial.
The Excel program will perform the
calculations using the insert function.
To get started click on the start menu,
highlight the programs folder, go over and highlight the Microsoft
Office folder, highlight Excel and left click on it to open the
program.
After the program is open you
are ready to input the binomial probability distribution. The following
steps will guide you through this process.
The open Excel page will
appear similar to the image shown.
In order to complete a trial,
select a cell and input the following data in the spread sheet start in
cell 1A for this purpose.
A1
X Probability of Distribution
A2 0
A3 1
A4 2
A5 3
A6 4
A7 5
A8 6
Compute the probability for
the following problem:
QUOTE
n = 6
The following steps can be
found in the book Basic Statistics Using Excel for Office XP 2005,
chapter six by Lind. This method can also be use in Excel 2007. Some of
the icons/functions are in different locations in Excel 2007 than
earlier versions.
1.
Click on the insert function button this
is found on the formula bar or click the formulas tab and click the
insert function button.
The insert function dialog box
will open.
2.
Click the drop down menu arrow of the Or select a category
and select Statistical.
3.
Choose the BINOMIST in the Select a function list box
and click OK
The BINOMIDST dialog box has the
following text boxes to fill:
The Number_s text box is for the number
of observes success, referred to as x.
Trials text box is for the number of
trials, referred to as n.
Probability_s text box is for the
probability of success on each trial, referred to as π.
Cumulative text box is used to indicate whether x, the number of
observed successes, is cumulative or not cumulative. In the Cumulative
box, key in 1 for true if you want the cumulative probability that
includes the numbers up to and including the value of x. You
would key 0 for false (or not cumulative) if x is the number of
observed successes only.
When you click on BINOMIDST the function
arguments box will open.
4.
Input the information as follows:
Number_s 0 or corresponding cell such as
D4.
Trials 6
Probability_s .10
Cumulative 0 or False or not cumulative
in this case.
5.
Click Ok. With the trials computed the value is .531441 in
cell B2.
6.
Select cell B2 and position the cursor at the bottom right
corner of the cell until it becomes a thick black plus sign. When
the cursor becomes a plus sign, click, hold and drag the cursor to
cell B8. The probability amounts will automatically fill in the
cells. After this is complete click on the AutoSum icon on the upper
left home tab or under the formula tab. The values should always
equal 1 or 100 percent.
The table above should be completed as
follows:
X

Probability of
Occurrence

0 
0.531441 


1 
0.354294 


2 
0.098415 


3 
0.01458 


4 
0.001215 


5 
5.4E05 


6 
0.000001 



1 







In conclusion, this is an example of how
to input a binomial distribution into the Excel program. For further
information, consult the Microsoft web page as well as the help function
(F1) in Excel.
Microsoft Office web page
office.microsoft.com/enus/default.aspx
has helpful information
and tutorials for the Excel program.
This web site has a tutorial
for earlier versions of Excel. I t can also be followed for Excel 2007.http://gator.dt.uh.edu/~hodgess/Stat1/prob1.html.
References
Lind, D. A. & Marchal, W. G. &
Wathen, S. A. (2004). Statistical techniques in business and
economics, 12e: Chapter 6: Binomial Probability Distribution.
New York: The McGrawHill Companies.
Lind, D. A. & Marchal, W. G. &
Wathen, S. A. (2005). Basic Statistics Using Excel for Office
XP for use with Statistical techniques in business and
economics, 12e: Chapter 6: Discrete Probability Distribution.
New York: The McGrawHill Companies.
