Descriptive Statistics Comparison and Implementation

Descriptive Statistics for Quantitative Variables: Cumulative Perspective

• Analyzing quantitative data requires examining the distribution of outcomes beyond simple frequency tables and histograms.

• The cumulative perspective utilizes tools such as the Empirical Cumulative Distribution Function ($F\hat(x)$, also known as ECDF) and boxplots to visualize data distribution.

• Key summary measures in this perspective include percentiles, specifically the median ($Q_2$), the first quartile ($Q_1$), the third quartile ($Q_3$), and the interquartile range (IQR).

• Techniques for analysis vary depending on whether the variable is discrete, continuous (raw data), or continuous (binned data).

Discrete Variables and the Empirical Cumulative Distribution Function

• Analysis of discrete variables relies heavily on the frequency table, specifically the cumulative relative frequencies ($c.r.f.$).

• Definition of ECDF ($F\hat(x)$): The goal is to represent the cumulative relative frequency as a step function. For values smaller than the first outcome, the function is $0$. At each discrete outcome, the function "jumps" to the next cumulative relative frequency value and remains horizontal until the next outcome is reached.

• Example Plotting Points: For a dataset where $F\hat(1) = 0.05$ and $F\hat(2) = 0.10$, the step function connects coordinates in a sequence: $(0, 0)$, $(1, 0)$, $(1, 0.05)$, $(2, 0.05)$, $(2, 0.1)$, and so on until reaching $1$.

Step-by-Step Construction of the Discrete ECDF in Excel

• Preparing Auxiliary Columns:

  • Copy the original outcomes (e.g., cells C6:C16) into a new column twice (e.g., I4:I14 and I15:I25).

  • Sort this combined range in ascending order so each outcome appears twice consecutively.

  • Define a corresponding Y-value column (Fhat). For each outcome that appears twice, the first Y-value should be the cumulative relative frequency of the previous outcome to create the horizontal level. The second Y-value should be the cumulative relative frequency of the current outcome to create the vertical jump.

• Logic Functions for Construction:

  • =INDEX(range, row_num, [col_num]): This function returns a value from a specific position in a range. For example, =INDEX(A2:C4, 2, 3) returns the value in cell C3 (2nd row, 3rd column). If only one column is provided, only the row number is needed.

  • =INT(number): Rounds a number down to the nearest integer. For instance, =INT(1.6) results in $1$.

  • =IF(logical_test, [value_if_true], [value_if_false]): Used to determine if the current X-value matches the previous one to decide whether to copy the previous cumulative value (horizontal segment) or pull the new cumulative relative frequency (vertical jump).

• Charting the Function:

  • Select the auxiliary range ($X$ and $Y$ values).

  • Go to Insert > Chart > X Y (Scatter).

  • Change the subtype to Scatter with Straight Lines.

  • Adjust axis bounds: Set the X-axis minimum to $0$ and maximum to the highest outcome (e.g., $10$). Set the Y-axis minimum to $0$ and maximum to $1$.

  • Interpretation Note: Only the horizontal segments are technically part of the step function; vertical lines are artifacts of the line-connecting feature in Excel and should be ignored.

Discrete Variable Percentiles and Boxplots

• Percentile Definition for Discrete Variables: The $k$-th percentile is defined as the smallest outcome such that the cumulative relative frequency is at least $\frac{k}{100}$.

• Manual Construction: Excel does not have a simple command for discrete boxplots following this specific definition. They are typically constructed manually on paper by reading the values from the frequency table:

  • First Quartile ($Q_1$): Smallest outcome where $c.r.f. \geq 0.25$.

  • Median ($Q_2$): Smallest outcome where $c.r.f. \geq 0.50$.

  • Third Quartile ($Q_3$): Smallest outcome where $c.r.f. \geq 0.75$.

  • 90th Percentile: Smallest outcome where $c.r.f. \geq 0.90$.

Continuous Variables: Raw Data Analysis

• When raw data is available, the cumulative perspective relies on ordering outcomes from smallest to largest.

• Empirical Distribution Function for Continuous Raw Data:

  • Identify the sample size ($n$) using =COUNT(range).

  • Sort the raw data in ascending order.

  • Duplicate the data in an auxiliary column (each value appears twice) to create the step function logic similar to discrete variables.

  • Use =IF(M4=M3, COUNTIF($A$2:$A$101, "<="&M4)/$H$2, N3) to calculate cumulative steps, where H2 is the sample size.

Percentile Calculation Strategies for Raw Data

• Different statistical software and calculators use slightly different strategies for percentiles. Most begin by finding the theoretical position of the percentile in a sorted list of length $n$.

• TI-84/Interpolation Method:

  1. Calculate the position: $(n + 1) \times \frac{k}{100}$.

  2. If the position is an integer $i$, the percentile is the $i$-th observation in the sorted range.

  3. If the position is not an integer, take the average of the $i$-th and $(i+1)$-th observations, where $i$ is the integer immediately below the calculated position.

• Excel Built-in Functions:

  • =PERCENTILE.EXC(range, k): Uses the position $(n+1) \times \frac{k}{100}$. It uses linear interpolation between values rather than a simple average. This function excludes $k=0$ and $k=1$.

  • =PERCENTILE.INC(range, k): Uses the position $(n-1) \times \frac{k}{100} + 1$. It includes extreme values ($k=0$ and $k=1$).

  • =QUARTILE.INC / =QUARTILE.EXC: Specifically for quartiles ($1$, $2$, or $3$).

  • =MEDIAN(range): All standard definitions result in the same value for the median.

Boxplots for Raw Data in Excel

• Creation: Select raw data and navigate to Insert > Chart > Box and Whisker.

• Formatting:

  • Excel defaults to the PERCENTILE.EXC method. To change this, right-click the plot, select Format Data Series, and set the Quartile calculation field to Inclusive median (matching PERCENTILE.INC).

  • Mean Marker: Represented by an "$\times$" in the boxplot. This is selected by default via Show Mean Markers.

  • Cleaning: Remove group labels ($1$) on the X-axis and add clear Y-axis labels.

Continuous Variables: Binned Data Analysis

• When raw data is unavailable and only a frequency table with bins is provided, interpolation is necessary.

• ECDF for Binned Data:

  • The ECDF is generated based on the cumulative relative frequencies of the upper bounds of each bin.

  • The graph starts at the lowest lower bound with a $c.r.f.$ of $0$.

  • Instead of a step function, points are joined by straight lines (linear interpolation), resulting in a continuous graph.

• Percentiles via Linear Interpolation:

  • Percentiles must be calculated manually using the formula based on the frequency table.

  • Example Calculation ($Q_1$): If the cumulative relative frequency for the bin edge $5.6$ is $0.21$ and for $6.2$ is $0.45$, the first quartile ($0.25$) lies within the bin $]5.6, 6.2]$.

  • Formula: $Q_1 = \text{Lower Bound} + \frac{0.25 - c.r.f._{\text{lower}}}{c.r.f._{\text{upper}} - c.r.f._{\text{lower}}} \times (\text{Upper Bound} - \text{Lower Bound})$

  • Using provided values: $Q_1 = 5.6 + \frac{0.25 - 0.21}{0.45 - 0.21} \times (6.2 - 5.6)$.

Summary of Analytical Goals by Data Type