Excel Statistical Formulas

Excel Statistical Formulas

Overview

Statistical functions in Excel calculate measures like mean, median, percentiles, and enable weighted calculations for economic data analysis.

Core Statistical Functions

Basic Descriptive Statistics

MIN - Returns the smallest value

Copy code
=MIN(range)
=MIN(H35:L40)

MAX - Returns the largest value

Copy code
=MAX(range)
=MAX(H35:L40)

MEDIAN - Returns the middle value

Copy code
=MEDIAN(range)
=MEDIAN(H35:L40)

AVERAGE - Returns the arithmetic mean

Copy code
=AVERAGE(range)
=AVERAGE(H35:L40)

Percentile Functions

PERCENTILE - Returns the kth percentile of a dataset

Copy code
=PERCENTILE(range, k)

Where k is a decimal (0.25 for 25th percentile, 0.75 for 75th percentile)

Examples:

Copy code
25th Percentile:  =PERCENTILE(H35:L40, 0.25)
75th Percentile:  =PERCENTILE(H35:L40, 0.75)

PERCENTILE.INC vs PERCENTILE.EXC:

• PERCENTILE.INC: Inclusive method (older PERCENTILE function)
• PERCENTILE.EXC: Exclusive method (newer, statistical best practice)

Weighted Mean Calculation

SUMPRODUCT for Weighted Mean

The SUMPRODUCT function multiplies corresponding elements in arrays and returns the sum of products.

Syntax:

Copy code
=SUMPRODUCT(array1, array2, ...)

Weighted Mean Formula:

Copy code
=SUMPRODUCT(values_array, weights_array) / SUM(weights_array)

Example: Weighted Mean of Net Exports by GDP Weight

Copy code
=SUMPRODUCT(G35:G40, GDP_weights) / SUM(GDP_weights)

How it works:

1. Multiply each value by its corresponding weight
2. Sum all products
3. Divide by sum of weights

Handling Multiple Columns

For data across multiple columns (like multiple years), you may need to:

1. Flatten the data into single rows/columns, or
2. Use array formulas with SUMPRODUCT

Example with multiple criteria:

Copy code
=SUMPRODUCT((range1=criteria1)*(range2=criteria2)*values) / SUMPRODUCT((range1=criteria1)*(range2=criteria2)*weights)

Percentage Calculations

Converting to Percentage Display

Excel stores percentages as decimals (0.123 = 12.3%).

To display as percentage (12.3 instead of 0.123):

Copy code
=formula_result * 100

To round to one decimal place:

Copy code
=ROUND(formula_result * 100, 1)

Combined example:

Copy code
=ROUND(SUMPRODUCT(values, weights) / SUM(weights) * 100, 1)

Practical Example: GCC Net Exports Analysis

Step 1: Calculate Net Exports Percentage

Copy code
=Net_Exports / GDP * 100

Step 2: Calculate Statistics

Copy code
Min:       =MIN(values) * 100
Max:       =MAX(values) * 100
Median:    =MEDIAN(values) * 100
Mean:      =AVERAGE(values) * 100
25th %ile: =PERCENTILE(values, 0.25) * 100
75th %ile: =PERCENTILE(values, 0.75) * 100

Step 3: Weighted Mean

Copy code
=SUMPRODUCT(values, weights) / SUM(weights) * 100

Where:

• values: Net exports as % of GDP
• weights: GDP figures (or country weights)

Rounding Best Practices

ROUND function:

Copy code
=ROUND(number, num_digits)

• num_digits = 1 for one decimal place
• num_digits = 0 for whole numbers
• num_digits = -1 for tens place

Example:

Copy code
=ROUND(12.345 * 100, 1)  → 1234.5
=ROUND(0.12345 * 100, 1) → 12.3

Performance Considerations

1. SUMPRODUCT with conditions: More flexible than SUMIFS, but slower on large datasets
2. Array formulas: Use where needed but consider calculation time
3. Volatile functions: MIN, MAX, PERCENTILE may recalculate frequently

Tips for Economic Data

1. Handling missing data: Use IFERROR with statistical functions

   Copy code
   =IFERROR(PERCENTILE(range, 0.25), "N/A")
   

2. Weighted by GDP: When calculating regional averages, weight by GDP size

   Copy code
   =SUMPRODUCT(country_values, country_gdp) / SUM(country_gdp)
   

3. Multi-year analysis: Calculate across years while maintaining criteria

4. Sensitivity: Test with subsets to validate formula logic