Excel for Binomial Distribution Probabilities

Using Excel for Binomial Distribution Calculations

Introduction

  • Excel can be used to perform computations for the binomial distribution.
  • Two Excel functions related to binomial distribution will be discussed.
  • These functions require inputs: n (number of trials), k (number of successes), and p (probability of success on a single trial).

Motivating Example: Assigning Grades

  • Example: A class of 20 students.
  • Probability of any student getting an A is 0.4.
  • Assume student grades are independent.

1. Calculating the Probability of k or Fewer Successes

  • Excel function: BINOM.DIST
  • Syntax: BINOM.DIST(number_s,trials,probability_s,cumulative)
    • number_s: Number of successes.
    • trials: Number of trials (n).
    • probability_s: Probability of success on any trial (p).
    • cumulative: Logical value determining the type of calculation.
      • TRUE: Cumulative distribution function (probability of k or fewer successes).
      • FALSE: Probability mass function (probability of exactly k successes).

Example Implementation

  • Problem: Find the probability that seven or fewer students receive A's in a class of 20, where the probability of any student getting an A is 0.4.
  • Excel implementation: =BINOM.DIST(7, 20, 0.4, TRUE)
  • Result: Approximately 0.416 or 41.6%.

2. Calculating the Probability of More Than k Successes

  • The probability of more than k successes is equivalent to 1 - P(k \text{ or fewer successes}).
  • In Excel, we calculate this by subtracting the result of BINOM.DIST(k, n, p, TRUE) from 1.

Example Implementation

  • Problem: Find the probability that more than seven students receive A's.
  • Calculation: 1 - P(\text{7 or fewer students get A's})
  • Excel implementation: =1 - BINOM.DIST(7, 20, 0.4, TRUE)
  • Result: Approximately 0.584 or 58.4%.

3. Calculating the Probability of Successes Within a Range

  • To calculate the probability of the number of successes falling within a range.

  • Excel function: BINOM.DIST.RANGE

  • Syntax: BINOM.DIST.RANGE(trials,probability_s,number_s,number_s2)

    • trials: Number of independent trials.
    • probability_s: Probability of success on each trial.
    • number_s: Lower bound of the range (inclusive).
    • number_s2: Upper bound of the range (inclusive).

Example Implementation

  • Problem: Find the probability that between five and nine students (inclusive) receive A's.
  • Excel implementation: =BINOM.DIST.RANGE(20, 0.4, 5, 9)
  • Result: Approximately 0.704 or 70.4%.

Summary

  • Excel can be used to calculate:
    • Probability of k or fewer successes using BINOM.DIST(k, n, p, TRUE).
    • Probability of more than k successes using 1 - BINOM.DIST(k, n, p, TRUE).
    • Probability of successes within a range using BINOM.DIST.RANGE(n, p, lower_bound, upper_bound).
  • This lecture transitions from discrete distributions (like the binomial) to continuous distributions, beginning with the normal distribution.