Chapter 6 — Excel Help

Fast instructions to finish Chapter 6 homework using Excel’s built-ins: Descriptive Statistics (ToolPak), Histogram, Box & Whisker, and a simple Normal Q–Q plot.
Prefer video? Quick Q-Q Plot walkthrough (YouTube)

1) Descriptive Statistics (Analysis ToolPak)

Enable the ToolPak

Run it (Win/Mac): Data → Data Analysis → Descriptive Statistics. Select your grades range (e.g., A2:A1000), check Summary statistics, choose an output location, OK.

Output contains: n, mean, median, standard deviation, min, max, range, etc. Use this to answer the descriptive-stats items (no formulas needed).

2) Histogram (built-in chart)

  1. Select the Statics_Grade column (header optional).
  2. Insert → Statistic Chart → Histogram.
  3. Right-click horizontal axis → Format Axis → Bins: set Bin width or Number of bins (e.g., 8) to match the homework.
Prefer a frequency table? Use: =FREQUENCY(data, bins) (dynamic array), then insert a Column chart from that output.

3) Box & Whisker (built-in chart)

  1. Select the Statics_Grade column (or several adjacent columns to compare groups).
  2. Insert → Statistic Chart → Box & Whisker.
  3. To show suspected outliers: Format Data Series → check Show inner/outer points.
Excel’s quartiles are slightly different from textbook definitions. For the classic 1.5×IQR outlier rule in a table, compute Q1/Q3 with QUARTILE.INC.

4) Normal Q–Q Plot — make the **z** column first

Goal: X = theoretical normal scores z, Y = your sorted grades. Then add a straight reference line using mean + SD.

Reference video: Normal Q–Q in Excel (YouTube)

A) Excel 365 quick build (with explicit Rank j)

  1. Put raw grades in A2:A1000 (or shorter).
  2. B2 — Rank j (spill 1..n):
    =SEQUENCE(COUNTA(A2:A1000))
  3. C2 — Plotting position pj (Hazen):
    =(B2#-0.5)/COUNTA(A2:A1000)
    Blom: =(B2#-0.375)/(COUNTA(A2:A1000)+0.25) • Weibull: =B2#/(COUNTA(A2:A1000)+1)
  4. D2 — z (theoretical normal quantiles):
    =NORM.S.INV(C2#)
  5. E2 — Sorted grades (skip blanks):
    =SORT(FILTER(A2:A1000, A2:A1000<>""))
  6. Reference line parameters (any cells):
    • F2 — mean:
      =AVERAGE(E2#)
    • F3 — SD (sample):
      =STDEV.S(E2#)
  7. Reference line endpoints (use z = −3 and 3):
    • H2:
      =-3
    • H3:
      =3
    • I2:
      =$F$2+$F$3*H2
    • I3:
      =$F$2+$F$3*H3
  8. Make the chart → Insert → Scatter (Markers):
    • Series 1 (data): X = D2# (your z), Y = E2# (sorted grades)
    • Series 2 (line): X = H2:H3, Y = I2:I3, format as a line

5) Quick Notes

  • Use the Descriptive Statistics output (ToolPak) for n, mean, median, s, min, max, range.
  • Use Histogram axis options to match the required bin count or width.
  • Use Box & Whisker to compare medians/IQR and spot suspected outliers.
  • Use the Q–Q helper formulas above to judge normality quickly.