📊

Confidence Intervals in Spreadsheets

Oct 27, 2025

Overview

This lecture explains how to calculate confidence intervals for the mean using spreadsheets, covering cases when the population or sample standard deviation is known or unknown.

Calculating Z Intervals (Population Std Dev Known)

  • Z intervals are used when the population standard deviation (σ) is known.
  • Given: sample mean (xÌ„), sample size (n), population standard deviation (σ), and confidence level.
  • Critical value (z*) is found using the norm.inv function: negative of norm.inv(left-tail area, 0, 1).
  • Left-tail area = (1 - confidence level)/2.
  • Margin of error (E) = z* × (σ / √n).
  • Confidence interval: lower bound = xÌ„ - E; upper bound = xÌ„ + E.

Inputting Sample Data in Spreadsheets

  • Input sample data into a column; calculate sample mean using the average function.
  • Use the count function for sample size.
  • Mark cells as inputs (changeable) and calculations (fixed) for usability.

Calculating T Intervals (Population Std Dev Unknown)

  • T intervals are used when the population standard deviation is unknown; use sample standard deviation (s).
  • Calculate sample mean and standard deviation from sample data.
  • Use t.inv.2t(1-confidence level, degrees of freedom) to find the t critical value (t*).
  • Degrees of freedom = n - 1.
  • Margin of error = t* × (s / √n).
  • Confidence interval: xÌ„ ± margin of error.

Determining Required Sample Size

  • Used when desired margin of error and confidence level are given.
  • Given: population standard deviation (σ), desired margin of error (E), confidence level.
  • Find z* using norm.inv as before.
  • Formula: n = [ (z* × σ) / E ]², rounded up to the nearest whole number.

Key Terms & Definitions

  • Confidence Interval — range where the population mean is expected to fall, with a given confidence level.
  • Critical Value (z, t)** — point on the distribution associated with the chosen confidence level.
  • Margin of Error (E) — maximum expected difference between sample mean and true population mean.
  • Degrees of Freedom — n - 1, used in t-distributions.
  • Z Interval — confidence interval using the normal distribution (σ known).
  • T Interval — confidence interval using the t-distribution (σ unknown).

Action Items / Next Steps

  • Practice creating spreadsheet templates for Z and T intervals.
  • Complete homework problems 8.2, 8.7, and related sample size calculations.
  • Review definitions and formulas for confidence intervals.

Full transcript