(Click On Image To See a Larger Version)
Overview of the Binomial
Distribution
This is one of the following eleven articles on creating user-interactive graphs of statistical distributions in Excel
Interactive Statistical Distribution Graph in Excel 2010 and Excel 2013
Interactive Graph of the Normal Distribution in Excel 2010 and Excel 2013
Interactive Graph of the Chi-Square Distribution in Excel 2010 and Excel 2013
Interactive Graph of the t-Distribution in Excel 2010 and Excel 2013
Interactive Graph of the t-Distribution’s PDF in Excel 2010 and Excel 2013
Interactive Graph of the t-Distribution’s CDF in Excel 2010 and Excel 2013
Interactive Graph of the Binomial Distribution in Excel 2010 and Excel 2013
Interactive Graph of the Exponential Distribution in Excel 2010 and Excel 2013
Interactive Graph of the Beta Distribution in Excel 2010 and Excel 2013
Interactive Graph of the Gamma Distribution in Excel 2010 and Excel 2013
Interactive Graph of the Poisson Distribution in Excel 2010 and Excel 2013
The binomial distribution is a family of discrete probability distributions. This is evidenced by the stepwise shape of the above graph of a binomial distribution’s PDF (Probability Density Function) curve. The stepwise shape of a discrete distribution indicates that the discrete distribution can only assume discrete values and is not continuous.
The binomial distribution calculates the probability of attaining k successes in N successive independent binary trials when each trial has the same probability p of a positive output. Each individual binary trial is called a Bernouli trial.
The classic binomial distribution example is to calculate the probability of a specific number of heads occurring a fair coin is flipped N times. A fair coin has a probability of success (heads) of p = 0.5. The single sample is made up of N independent trials or flips.
The binomial distribution describes the distribution of the number of successes, X, if the following four conditions exists:
1) Each trial is a single Bernoulli trial, which is a binary event having only one of two outcomes: success or failure.
2) The total number of trials = N.
3) Each trial is independent of all other trials.
4) The probability of success in each trial is p, which is constant in all trials. The probability of failure = q = 1 – p.
The binomial distribution is used to model the number of successes when each sample taken is replaced back into the population. This is called sampling with replacement. If the sampling is carried out without replacement, the hypergeometric distribution should be used in place of the binomial distribution to calculate the probability of the number of success in N trials. If the sample size (N = the number of trial, i.e., the sample size) is much smaller than the size of the population from which the sample is taken, the binomial distribution provides a close approximation of the hypergeometric distribution.
As a general rule, the binomial distribution should only be used if the population size is at least 10 times larger than the sample size N.
Graphing the Binomial
Distribution’s PDF – Probability
Density Function
Step 1 – Create a Count
The Count becomes the basis for the X and Y values of each data point on the graph. This count will contain 100 points that count from 0 to 100 in increments 1. There are many ways to create a count. This count uses the method ROW() – ROW($B) to increment each cell value in the column by 1.
(Click On Image To See a Larger Version)
Step 2 – Create the Count of Positive Outcomes
The Y values of each point on the graph calculate the probability of a specific number of positive outcomes occurring in N trials given that p equals the probability of a positive outcome on each trial. The number of positive outcomes must therefore be calculated from each increment of the count.
The If-Then-Else statement is used to calculate the number of positive outcomes in order to prevent a negative number of positive outcomes. The Excel BINOM.DIST() formula creates an error if the input number of positive values is negative.
(Click On Image To See a Larger Version)
Step 3 – Create the X Values
The X values for each data point will each the number of positive values.
(Click On Image To See a Larger Version)
Step 4 – Create the Y Values
One Y value is created for each increment of the Count. The Y value of each data point is its PDF value. The binomial distribution’s PDF value requires the count of positive values, the number of trials (N), and the probability of a positive outcome on each trial (p). The X axis often has to be shifted and expanded in order to view the entire PDF curve fully in a single graph.
(Click On Image To See a Larger Version)
The following Excel-generated graph shows the binomial distribution’s PDF (Probability Density Function) for as the X value (number of positive outcomes) goes from 37 to 62 with N = 100 and p = 0.5.
The PDF value of a statistical distribution (the Y value) at a specific X value equals the probability that the value of a random sample will be equal to that X value if the population of data values from which the sample was taken is distributed according the stated distribution. The CDF value of a statistical distribution (the Y value) at a specific X value equals the probability that the value of a random sample will be up to that X value.
(Click On Image To See a Larger Version)
The process of creating an Excel area chart and connecting the user inputs to the chart is shown in detail in the section of this manual that provides instructions on how to create an interactive normal distribution PDF curve with outer tails.
Effect of Changing the p Value
The following Excel-generated graph shows the binomial distribution’s PDF (Probability Density Function) for as the X value (number of positive outcomes) goes from 37 to 62 with N = 100 and p is increased from 0.5 to 0.6.
Effect of Changing the N Value
The following Excel-generated graph shows the binomial distribution’s PDF (Probability Density Function) for as the X value (number of positive outcomes) goes from 37 to 59 with p = 0.5 and N is decreased from 100 to 90.
(Click On Image To See a Larger Version)
Effect of Shifting the X Axis
(Click On Image To See a Larger Version)
Effect of Expanding or Contracting the X Axis
(Click On Image To See a Larger Version)
Graphing the Binomial
Distribution’s CDF – Cumulative
Distribution Function
The following Excel-generated graph shows the Binomial distribution’s CDF (Cumulative Distribution Function) for 10 degrees of freedom as the X value (the number of positive outcomes) goes from 37 to 62 if the probability of a positive outcome on each of 100 binary trails equals 0.5.
The CDF value of a statistical distribution (the Y value) at a specific X value equals the probability that the value of a random sample will be up to that X value if the population of data values from which the sample was taken is distributed according the stated distribution. The PDF value of a statistical distribution (the Y value) at a specific X value equals the probability that the value of a random sample will be equal to that X value.
(Click On Image To See a Larger Version)
Excel Master Series Blog Directory
Click Here To See a List Of All
Statistical Topics And Articles In
This Blog
You Will Become an Excel Statistical Master!
Good evening guys! Our custom research paper writing service https://essaysrescue.com/essaypro-review/ seeks to boost your intellectual abilities and prove to your supervisor that you are on the right program. With a team of PhD and MA experts on board, you will get a tailored thesis that meets all the required specifications.
ReplyDeleteHey visitors! Now I am working as an author at one huge educational company. If you like to read or you need nursing essay help, then follow the professional writing agency’s site, where you could order correct papers. And we can help with any study problem you really need to!!!
ReplyDelete