run2_excel-statistics-aggregates

Excel Statistical and Aggregate Functions

Central Tendency Functions

AVERAGE (Arithmetic Mean)

Returns the arithmetic mean of a range.

=AVERAGE(H35:H40)

• Simple mean across all values
• Treats all values equally
• Returns single decimal value

MEDIAN

Returns the middle value in a sorted range.

=MEDIAN(H35:H40)

• Middle value when data is sorted
• For 6 values: average of 3rd and 4th values
• Robust to outliers

Extreme Values

MIN

Returns the minimum value in a range.

=MIN(H35:H40)

MAX

Returns the maximum value in a range.

=MAX(H35:H40)

Percentile Functions

QUARTILE (Excel 2007+) - Recommended

Returns quartile values for a dataset.

Syntax: =QUARTILE(range, quart)

Parameters:

• range: Data range to analyze (e.g., H35:H40)
• quart: Quartile number (0-4)
  • 0: Minimum value
  • 1: 25th percentile (Q1)
  • 2: Median/50th percentile
  • 3: 75th percentile (Q3)
  • 4: Maximum value

Examples:

=QUARTILE(H35:H40, 1)   # 25th percentile
=QUARTILE(H35:H40, 3)   # 75th percentile

PERCENTILE (Alternative Syntax)

Returns percentile value with flexible precision.

Syntax: =PERCENTILE(range, k)

Parameters:

• range: Data range
• k: Percentile value (0 to 1)
  • 0.25 for 25th percentile
  • 0.5 for 50th percentile (median)
  • 0.75 for 75th percentile

Examples:

=PERCENTILE(H35:H40, 0.25)   # 25th percentile
=PERCENTILE(H35:H40, 0.75)   # 75th percentile

Weighted Aggregates

SUMPRODUCT (Weighted Sum and Weighted Mean)

For weighted sum:

=SUMPRODUCT(values_range, weights_range)

Multiplies each value by its weight, then sums results.

For weighted mean:

=SUMPRODUCT(values_range, weights_range) / SUM(weights_range)

Example - Weighted mean of GDP by country:

=SUMPRODUCT(H35:H40, H26:H31) / SUM(H26:H31)

Example - Aggregate net exports as % of GDP for region:

=(SUM(Exports) - SUM(Imports)) / SUM(GDP) * 100

Rounding Statistical Results

Always round when displaying statistics as percentages or to specific decimal places:

=ROUND(MIN(H35:H40), 1)
=ROUND(MAX(H35:H40), 1)
=ROUND(MEDIAN(H35:H40), 1)
=ROUND(AVERAGE(H35:H40), 1)
=ROUND(QUARTILE(H35:H40, 1), 1)
=ROUND(QUARTILE(H35:H40, 3), 1)

Statistical Function Selection Guide

Need	Function	Example
Middle value	MEDIAN	=MEDIAN(range)
Average	AVERAGE	=AVERAGE(range)
Smallest	MIN	=MIN(range)
Largest	MAX	=MAX(range)
25th percentile	QUARTILE(...,1)	=QUARTILE(range,1)
75th percentile	QUARTILE(...,3)	=QUARTILE(range,3)
Weighted average	SUMPRODUCT	=SUMPRODUCT(vals,wts)/SUM(wts)
Aggregate %*	SUM ratio	=(SUM(E)-SUM(I))/SUM(G)*100

*For aggregate metrics like GCC-wide net exports %

Performance Considerations

• SUMPRODUCT vs. array formulas: SUMPRODUCT is simpler and more compatible across Excel versions
• Data range size: All functions handle large ranges efficiently (tested up to millions of rows)
• Empty cells: Most functions ignore empty cells; verify if zeros should be included

Common Patterns for Data Analysis

Compare Individual vs. Aggregate

Individual (per country): =ROUND(AVERAGE(H35:H40), 1)
Aggregate (for region): =ROUND((SUM(H12:H17)-SUM(H19:H24))/SUM(H26:H31)*100, 1)

Detect Outliers Using Quartiles

Q1: =QUARTILE(range, 1)
Q3: =QUARTILE(range, 3)
IQR: =Q3 - Q1
Outlier threshold: Values > Q3 + 1.5*IQR or < Q1 - 1.5*IQR

Multi-Dimensional Aggregation

=SUMPRODUCT(criteria_range=criteria_value, aggregate_range) / SUMPRODUCT(criteria_range=criteria_value, weight_range)