Descriptive Statistics: Measures of Spread, Standard Deviation, Empirical Rule, and Excel Implementation

Box Plots, Outliers, and Five-Number Summary

• Box Plot Structural Components:

  • The horizontal or vertical navy blue lines extending from the central box are defined as the whiskers.

  • Whiskers extend to capture the range of non-outlier data points up to calculated threshold bounds (fences).

  • Any data point extending beyond the whisker bounds is classified as an outlier.

  • The complete five-number summary displayed via a box plot includes:

  1. Minimum

  2. First Quartile ($Q_1$ / $25\text{th}$ percentile)

  3. Median ($Q_2$ / $50\text{th}$ percentile)

  4. Third Quartile ($Q_3$ / $75\text{th}$ percentile)

  5. Maximum

• Interquartile Range ($IQR$):

  • Defines the distance spanning the middle $50\%$ of the dataset:     $IQR = Q_3 - Q_1$

  • While $IQR$ measures central dispersion, it does not account for the total spread across all individual observations in a dataset, necessitating comprehensive variance metrics.

Variance and Standard Deviation Concepts

• Individual Deviations from the Mean:

  • Deviation measures how far a specific data observation ($x_i$) lies from the arithmetic mean ($\bar{x}$):     $\text{Deviation} = x_i - \bar{x}$

  • Individual deviation values can be positive (when the observation is greater than the mean) or negative (when the observation is less than the mean).

  • Across any dataset, the sum of raw deviations always equals zero:     $\sum (x_i - \bar{x}) = 0$

  • Because positive and negative deviations perfectly cancel each other out, taking the raw sum or unadjusted average of deviations yields zero and cannot be used to measure dataset variability.

• Mechanics of Squaring Deviations:

  • Squaring individual deviations eliminates negative signs, forcing all transformed deviations to be non-negative.

  • Squaring exaggerates larger deviations relative to smaller ones, heavily penalizing extreme values and making outliers easily identifiable.

• Unit Transformation and the Need for Standard Deviation:

  • When deviations are squared, the measurement unit is squared as well (e.g., annual spend in dollars becomes $\text{dollars}^2$).

  • Variance is expressed in squared units ($\text{dollars}^2$), which lacks direct intuitive meaning for real-world interpretation.

  • Taking the square root of variance converts the metric back to the original units of measurement ($\text{dollars}$), producing the Standard Deviation ($s$ or $\sigma$).

  • Standard deviation quantifies the typical standard distance or spread of customer spend relative to the mean.

Mathematical Formulas for Variance and Standard Deviation

• Sample Variance ($s^2$):

  • Applied when analyzing sample data drawn from a larger population:     $s^2 = \frac{\sum (x_i - \bar{x})^2}{n - 1}$

  • The denominator uses $n - 1$ (degrees of freedom correction) rather than sample size $n$.

  • Dividing by $n$ instead of $n - 1$ when calculating sample variance is the single most common student calculation error.

• Population Variance ($\sigma^2$):

  • Applied only when the entire population dataset is available:     $\sigma^2 = \frac{\sum (x_i - \mu)^2}{n}$

  • Uses population size $n$ (or $N$) in the denominator.

  • Because population data is rarely obtainable in practice, sample formulas ($s^2$ and $s$) are overwhelmingly used.

• Sample Standard Deviation ($s$):   $s = \sqrt{s^2} = \sqrt{\frac{\sum (x_i - \bar{x})^2}{n - 1}}$

• Population Standard Deviation ($\sigma$):   $\sigma = \sqrt{\sigma^2} = \sqrt{\frac{\sum (x_i - \mu)^2}{n}}$

The Empirical Rule (68-95-99.7 Rule)

• Mandatory Prerequisite:

  • The Empirical Rule applies only when data is symmetric and bell-shaped (normally distributed).

  • It cannot be applied to skewed, non-uniform, or arbitrary distributions.

• Distribution Spread Intervals:

  • Approximately $68\%$ of observations lie within $\pm 1$ standard deviation of the mean ($\bar{x} \pm 1s$) (roughly two-thirds of all data points).

  • Approximately $95\%$ of observations lie within $\pm 2$ standard deviations of the mean (\bar{x} \n\pm 2s).

  • Approximately $99.7\%$ of observations lie within $\pm 3$ standard deviations of the mean ($\bar{x} \pm 3s$).

  • Only $0.3\%$ of observations in a normal distribution fall outside $\pm 3$ standard deviations.

• Mathematical Fallacy of Applying the Rule to Skewed Data:

  • Calculating empirical intervals on right-skewed spending data yields negative dollar lower bounds at $-1s$ or $-2s$.

  • Because customers cannot have negative dollar spend (extracting money from the firm), negative bounds confirm that the dataset is non-normal and that empirical rule outputs are invalid for skewed data.

Practical Application & Business Implications

• Customer Spend Case Study Metrics:

  • Mean customer annual spend: $\bar{x} = \$536$

  • Sample standard deviation: $s = \$343$

• Interpretation of High Standard Deviation:

  • An $SD$ of $\$343$ relative to a mean of $\$536$ indicates substantial spread and variability in individual customer behaviors.

  • Specific spending ranges widely (e.g., some customers spend $\$100$, whereas others spend $\$608$ or $\$1000$).

• Business and Marketing Strategy Implication:

  • High standard deviation prevents managers from treating the customer base as a homogeneous group based solely on the $\$536$ average.

  • A single generic marketing strategy or promotion will fail.

  • Marketers must design tiered promotions: one targeting low-spending customers to increase engagement, and another targeting high-spending customers to drive loyalty.

Step-by-Step Excel Implementation & Formulas

• Calculating the Five-Number Summary, IQR, and Outlier Fences:

  • Minimum: =MIN(range)

  • First Quartile ($Q_1$): =QUARTILE.INC(range, 1)

  • Median ($Q_2$): =QUARTILE.INC(range, 2)

  • Third Quartile ($Q_3$): =QUARTILE.INC(range, 3)

  • Maximum: =MAX(range)

  • Interquartile Range ($IQR$): =Q3_cell - Q1_cell

  • $1.5 \times IQR$ offset: =1.5 * IQR_cell

  • Lower Fence bound: =Q1_cell - (1.5_IQR_cell)

  • Upper Fence bound: =Q3_cell + (1.5_IQR_cell)

• Essential Rule for Cross-Sheet References:

  • When selecting a data column on a secondary tab (e.g., raw data), complete the argument entry, commas, and closing parenthesis while still viewing the raw data tab in the formula bar.

  • Pressing Enter directly from the source tab executes the formula correctly. Clicking back to the summary worksheet tab before closing the formula corrupts Excel's sheet cell references.

• Cell Locking with Absolute References:

  • Standard cell references shift relatively when dragged across rows or columns.

  • Inserting dollar signs before both column letter and row number (e.g., $B$14 or $B$19) locks the cell reference completely.

  • Hovering over the bottom right corner of a cell until the cursor converts to a solid black plus sign allows formula dragging without shifting fixed parameters.

• Logical IF Statements and Nested Syntax:

  • General IF Syntax: =IF(logical_test, value_if_true, value_if_false)

  • Example conditional test: =IF(B2 > 40, "above 40", "below 40")

  • Nested IF Statement for Outlier Detection:     =IF(B25 = "", "Missing", IF(B25 > $B$19, "Outlier High", "Normal"))

• Manual Variance Calculation Procedure ($n = 6$ Sample Demonstration):

  1. Count observation size $n$: =COUNT(B6:B11) (yields $n = 6$).

  2. Compute sample mean $\bar{x}$: =AVERAGE(B6:B11).

  3. Compute individual deviations $(x_i - \bar{x})$: =B6 - $B$14 (dragged across all $6$ observations).

  4. Square deviations $(x_i - \bar{x})^2$: =C6^2 using the caret symbol ^ (Shift + 6 key combo).

  5. Sum squared deviations: =SUM(D6:D11).

  6. Compute denominator $n - 1$: =6 - 1 (yields $5$).

  7. Calculate sample variance $s^2$: =Sum_Squared_Deviations / 5 (yields $\$24,600$).

  8. Calculate standard deviation $s$: =SQRT(Variance_cell) (yields $\$156.84$).

  9. Direct automated formula check: =STDEV.S(B6:B11) produces the exact same result.

• Full Dataset Implementation ($n = 200$):

  • Using =COUNTA(raw_data!E:E) across an entire column returns $201$ because it counts the text header row.

  • To highlight exact data ranges without headers, select the first data cell (E2) and press Ctrl + Shift + Down Arrow (E2:E201), obtaining the correct sample count $n = 200$.

  • Automated Sample Variance: =VAR.S(raw_data!E2:E201)

  • Automated Sample Standard Deviation: =STDEV.S(raw_data!E2:E201)

  • Automated Population Standard Deviation: =STDEV.P(raw_data!E2:E201)

  • Variance Comparison: On the dataset of $n = 200$, the difference between Sample SD (=STDEV.S) and Population SD (=STDEV.P) is $\$0.86$ (less than one dollar), caused by dividing by $199$ versus $200$.

Questions & Concept Checks

• Scenario Problem Setup:

  • Two independent coffee shop chains each serve an average of $200$ customers per day.

  • Chain A has a customer standard deviation $s = 8$ customers per day.

  • Chain B has a customer standard deviation $s = 75$ customers per day.

• Evaluation of Options:

  • Option A: Chain A has more customers per day than Chain B.

  • Incorrect: Both chains have identical daily customer means ($\bar{x} = 200$).

  • Option B: Chain B's customer data is very consistent and predictable.

  • Incorrect: High standard deviation indicates instability and unpredictability, not consistency.

  • Option C: Chain B experiences much greater day-to-day variability in customer numbers than Chain A.

  • Correct: An $SD$ of $75$ demonstrates extreme daily numerical fluctuation relative to an $SD$ of $8$

  • Option D: Chain B's data contains errors because an $SD$ of $75$ is mathematically impossible.

  • Incorrect: Standard deviation can take any non-negative numerical magnitude depending on dataset dispersion.

• Final Answer:

  • C (Chain B experiences much greater day-to-day variability in customer numbers).