Normal Distribution in Excel

Normal Distribution Functions in Excel

  • Excel has functions for working with the normal distribution.
  • These functions are important for hypothesis tests.
  • Some hypothesis tests assume a value is normally distributed.
  • Normal distribution functions help determine the probability of normally distributed events.
  • Example: Testing if most people are happy with their mates involves questioning people and using a normal distribution function.

Solving Normal Probability Problems with Excel

  • Scenario: Ordering textbooks for a statistics class.
  • Textbook production time is normally distributed with:
    • Mean ($\mu$) = 7 weeks
    • Standard Deviation ($\sigma$) = 2 weeks

Probability Between 6 and 8 Weeks

  • Problem: Find the probability that textbook production takes between 6 and 8 weeks.

  • Excel's normal distribution function (NORM.DIST) provides the cumulative distribution.

  • Steps:

    • Calculate the probability of taking fewer than 8 weeks.
    • Calculate the probability of taking fewer than 6 weeks.
    • Subtract the second from the first to find the area between 6 and 8 weeks.

  • Formula: P(6 < X < 8) = P(X < 8) - P(X < 6)

  • Excel Calculation:

    =NORM.DIST(8, 7, 2, TRUE) - NORM.DIST(6, 7, 2, TRUE)

  • Result: The probability is 0.3829, or 38.29%, which is less than 50%, so it's not typical.

Probability of More Than 10 Weeks

  • Problem: Find the probability that textbook production takes more than 10 weeks.

  • We want to find P(X > 10)

  • We can only directly calculate P(X < 10) with NORM.DIST.

  • Use the complement rule: P(X > 10) = 1 - P(X < 10)

  • Excel Calculation:

    =1 - NORM.DIST(10, 7, 2, TRUE)

  • Result: The probability is small, 0.067 or 6.7%.

Determining Order Lead Time

  • Problem: Determine how far in advance to order to ensure a 98% probability of on-time arrival.

  • Find a value 'a' such that P(X < a) = 0.98

  • This requires the inverse normal function (NORM.INV).

  • Use NORM.INV to find the value 'a' where the area to the left is 0.98.

  • Excel Calculation:

    =NORM.INV(0.98, 7, 2)

  • Result: We need to order 11.1 weeks in advance.

Excel Functions Summary

  • NORM.DIST: Returns the area under the normal distribution curve below a given value (cumulative distribution).
  • NORM.INV: Returns the value on the normal distribution for a given probability (the inverse of NORM.DIST).