Monday, June 2, 2014

Interactive Graph of Binomial Distribution in Excel 2010 and Excel2013

binomial, binomial distribution, graph, chart graph, excel, excel 2010, excel 2013, statistics(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.

binomial, binomial distribution, graph, chart graph, excel, excel 2010, excel 2013, statistics (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.

binomial, binomial distribution, graph, chart graph, excel, excel 2010, excel 2013, statistics (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.

binomial, binomial distribution, graph, chart graph, excel, excel 2010, excel 2013, statistics (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.

binomial, binomial distribution, graph, chart graph, excel, excel 2010, excel 2013, statistics (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.

binomial, binomial distribution, graph, chart graph, excel, excel 2010, excel 2013, statistics (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.

binomial, binomial distribution, graph, chart graph, excel, excel 2010, excel 2013, statistics (Click On Image To See a Larger Version)

 

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.

binomial, binomial distribution, graph, chart graph, excel, excel 2010, excel 2013, statistics (Click On Image To See a Larger Version)

 

Effect of Shifting the X Axis

binomial, binomial distribution, graph, chart graph, excel, excel 2010, excel 2013, statistics (Click On Image To See a Larger Version)

 

Effect of Expanding or Contracting the X Axis

binomial, binomial distribution, graph, chart graph, excel, excel 2010, excel 2013, statistics (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.

cbinomial, binomial distribution, graph, chart graph, excel, excel 2010, excel 2013, statistics (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!

No comments:

Post a Comment