Chapter 2 Activity — Descriptive Statistics in Excel

Researchers at TWU surveyed a sample of 30 studentsand recorded each student's one-way commute to campus, in minutes.

Download CSV

Open the file in Excel. The data is in column B (rows 2–31) under the headerCommute (min). If you prefer to type the numbers yourself, here they are:

n = 30 commute times, in minutes.

In Excel, paste (or type) the data so that:

• Cell B1 contains the header Commute (min)
• Cells B2:B31 contain the 30 values

Below the data (say, starting at row 34), set up a small “Summary” table with two columns: a label in column A and a formula in column B. You will fill it in over the next several parts.

In your summary table, enter these formulas:

Mean   =AVERAGE(B2:B31)
Median =MEDIAN(B2:B31)
Mode   =MODE.SNGL(B2:B31)
Count  =COUNT(B2:B31)

Record the mean, median, and mode. The mean uses the symbol x̄ for a sample (and μ for a population) — which one applies here?

Stop and think: compare the mean and the median. Which is larger? What does the difference suggest about the shape of the distribution?

Add the following formulas to your summary table:

Min  =MIN(B2:B31)
Q1   =QUARTILE.INC(B2:B31, 1)
Q2   =QUARTILE.INC(B2:B31, 2)        (this should equal MEDIAN)
Q3   =QUARTILE.INC(B2:B31, 3)
Max  =MAX(B2:B31)
IQR  =QUARTILE.INC(B2:B31, 3) - QUARTILE.INC(B2:B31, 1)

Write out the five-number summary in the order Min, Q1, Median, Q3, Max and interpret Q1 and Q3in plain English (e.g., “25% of students commute less than…”).

Outlier check. A value is a potential outlier if it falls below Q1 − 1.5·IQR or above Q3 + 1.5·IQR. Add these to your table:

Lower fence =Q1 - 1.5*IQR
Upper fence =Q3 + 1.5*IQR

Which observations in the data set, if any, qualify as outliers?

Add the spread formulas:

Range          =MAX(B2:B31) - MIN(B2:B31)
Sample variance     s²  =VAR.S(B2:B31)
Sample std. dev.    s   =STDEV.S(B2:B31)
Population variance σ²  =VAR.P(B2:B31)
Population std. dev σ   =STDEV.P(B2:B31)

Even though the same 30 numbers are in column B, VAR.S divides by n − 1 while VAR.P divides by N. Note which one you would report if these 30 students are a sample from a larger population (the usual situation).

Verify that variance = (standard deviation)² by squaring your s and confirming you get s².

1. Highlight B1:B31 (include the header).
2. Click Insert → Insert Statistic Chart → Histogram.
3. Right-click the horizontal axis → Format Axis → set Bin width to 10. (Try a few values: 5, 10, 15. How does the shape change?)

Describe the shape. Is it symmetric, right-skewed, or left-skewed? Is there one peak (unimodal) or more (bimodal)? Where is the bulk of the data, and where are the unusual values?

1. Highlight B1:B31.
2. Click Insert → Insert Statistic Chart → Box and Whisker.
3. Excel will draw the box from Q1 to Q3, the median line inside the box, whiskers to the most extreme non-outlier values, and any outliers as dots.

Comparethe boxplot with your fence calculations from §4. Do the dots in the chart match the values you flagged as outliers? Hover (or click) on the box to read off Excel's reported Q1, median, and Q3 — they should match your QUARTILE.INC values.

The z-score of a value x is z = (x − x̄) / s, the number of standard deviations x lies above (positive) or below (negative) the mean.

Suppose a 35-minute commute looks “long” to one student and a 12-minute commute looks “short” to another. Compute their z-scores. In Excel, use a cell reference for your mean and standard deviation:

Mean cell    e.g. B34       (= AVERAGE(B2:B31))
Std dev cell e.g. B38       (= STDEV.S(B2:B31))

z for 35 min   =(35 - $B$34) / $B$38
z for 12 min   =(12 - $B$34) / $B$38
z for 90 min   =(90 - $B$34) / $B$38     (the longest commute in the sample)

Or use Excel's built-in STANDARDIZE function:

=STANDARDIZE(35, $B$34, $B$38)

Which of those three commute times is “most unusual” — i.e., farthest from the mean in standard-deviation units?

In a free cell, copy the data set and replace the value 90 with 250 — imagine one student now has a much longer commute. Recompute the mean, median, standard deviation, and IQR on the modified data.

Fill in this comparison table in your worksheet:

Which statistics changed a lot, and which barely moved? This is what Chapter 2 calls the difference between resistant (median, quartiles, IQR) and not resistant (mean, standard deviation, range, variance) measures.

1. Report the mean, median, mode, range, sample standard deviation, IQR, and the five-number summary for the original data set.
2. Describe the shape of the distribution from your histogram. Does the relationship between the mean and the median agree with the shape you described?
3. List any outliers identified by your boxplot and by the 1.5·IQR rule. Do the two methods agree?
4. Looking at your z-scores, was the 90-minute commute more than 2 standard deviations from the mean? More than 3? What does that suggest?
5. If you had to summarize this data set with one measure of center and one measure of spread, which would you choose, and why?
6. Paste a screenshot (or printout) of your worksheet showing your summary statistics, histogram, and boxplot.