Area under a normal curve using Excel

Area Under a Normal Curve Using Excel

Norm Dot Dist Command

  • Used to compute the area under a normal curve in Excel.
  • Takes four arguments:
    • x: The value from the population for which we compute the area to the left.
    • Mean: The mean of the population.
    • Standard Deviation: The standard deviation of the population.
    • True/False: A true/false value; always use true for cumulative probability.

Example 1: Pregnancy Length

  • Pregnancy length is approximately normally distributed.
  • Mean (\mu) = 272 days
  • Standard deviation (\sigma) = 9 days

Proportion of Pregnancies Lasting Less Than 265 Days

  • Use NORM.DIST command.
  • Arguments: x = 265, mean = 272, standard deviation = 9, cumulative = TRUE.
  • Excel command: =NORM.DIST(265, 272, 9, TRUE)
  • Result: 0.21835. This means approximately 21.84% pregnancies last less than 265 days.

Proportion of Pregnancies Lasting Longer Than 280 Days

  • Find the area to the left of x = 280 using NORM.DIST.
  • Subtract this area from 1 to find the area to the right.
  • Excel command: =1 - NORM.DIST(280, 272, 9, TRUE)
  • Result: 0.1870. This means approximately 18.7% pregnancies last longer than 280 days.

Proportion of Full-Term Pregnancies (252 to 298 Days)

  • Find the area to the left of 252 and 298 using NORM.DIST.
  • Subtract the smaller area from the larger area to find the area between.
  • Excel command: =NORM.DIST(298, 272, 9, TRUE) - NORM.DIST(252, 272, 9, TRUE)
  • Result: 0.9849. This means approximately 98.49% of pregnancies are full term.

Finding a Normal Value Corresponding to a Given Area

Norm Dot Inverse Command

  • Used to find the value from a normal population with a given area to its left.
  • Takes three arguments:
    • Area (to the left)
    • Mean
    • Standard Deviation
  • The mean has an area of 0.5 to both its right and left.

Example 2: IQ Scores

  • IQ scores are normally distributed.
  • Mean (\mu) = 100
  • Standard deviation (\sigma) = 15

IQ Score Separating the Upper 2% from the Rest

  • Find the score x_1 such that the area to its right is 0.02.
  • The area to the left of x_1 is 0.98 (1 - 0.02 = 0.98).
  • Use NORM.INV command.
  • Arguments: area = 0.98, mean = 100, standard deviation = 15.
  • Excel command: =NORM.INV(0.98, 100, 15)
  • Result: 130.806. Rounded to the nearest whole number equals 131.

IQ Scores Separating the Middle 90% from the Rest

  • Find scores x1 and x2 that bound the middle area of 0.9.
  • The area of the two tails combined is 0.1.
  • The area of each tail is 0.05 (0.1 / 2 = 0.05).
  • The area to the left of x1 is 0.05, and to the left of x2 is 0.95.
  • For x_1: NORM.INV(0.05, 100, 15) = 75.3272
  • For x_2: NORM.INV(0.95, 100, 15) = 124.67287
  • Rounded to whole numbers, 90% of IQ scores are between 75 and 125.

Example 3: Smartphone Apps

  • Number of apps on a smartphone is normally distributed.
  • Mean (\mu) = 90
  • Standard deviation (\sigma) = 25

Finding the Third Quartile

  • The third quartile is the 75th percentile (0.75).
  • Use NORM.INV command.
  • Arguments: area = 0.75, mean = 90, standard deviation = 25.
  • Excel command: =NORM.INV(0.75, 90, 25)
  • Result: 106.86

Using Table A2

  • Find the area closest to 0.75 in the body of the table.
  • The corresponding z-score is approximately 0.67.
  • Compute x_1 = \mu + z \times \sigma
  • x_1 = 90 + 0.67 \times 25 = 106.75
  • The third quartile is approximately 106.75.