Tutorials›Excel›Statistical Functions
🟢 Free Demo
Excel TutorialTopic 22 of 31

Statistical Functions

Analyse data distributions with AVERAGE, MEDIAN, STDEV, CORREL, and other essential statistical Excel functions.

✅ What You Will Learn

›How to calculate mean, median, and mode with AVERAGE, MEDIAN, MODE
›How to measure spread with STDEV, VAR, and percentiles
›How to find correlations between two data series with CORREL
›How to use RANK to rank values within a dataset
›How to calculate weighted averages and moving averages

Statistical functions let you go beyond simple sums and counts to understand the distribution and relationships within your data. Mean, median, and standard deviation are the three most common statistics in business analysis — they appear in every HR analytics report, sales dashboard, and financial analysis.

Excel has two sets of statistical functions: the older functions (STDEV, VAR) which work on a sample, and the *P variants (STDEVP, VARP) which work on the entire population. In business analysis, you almost always have a sample — use STDEV, not STDEVP.

Understanding the difference between mean and median is especially important for salary and price data. The mean is pulled by outliers — one very high salary inflates the average. The median (middle value) is more representative when data is skewed.

📋 Employee salary data for statistical analysis

EmployeeDepartmentSalary (₹)Experience (Yrs)Rating
RahulSales48000034.2
PriyaTech92000074.8
AmitSales38000023.9
SnehaHR45000044.5
VikasTech1200000104.7
AnjaliSales52000054.1
DeepakTech75000064.3

Syntax

EXCEL SYNTAX
=AVERAGE(range)         → arithmetic mean
=MEDIAN(range)          → middle value (50th percentile)
=MODE.SNGL(range)       → most frequent value
=STDEV(range)           → standard deviation (sample)
=VAR(range)             → variance (sample)
=PERCENTILE(range, k)   → k-th percentile (0–1), e.g. 0.9 = 90th
=QUARTILE(range, q)     → q=1 (25th), q=2 (50th), q=3 (75th)
=CORREL(array1, array2) → correlation coefficient (-1 to +1)
=RANK(number, ref, [order]) → rank within a range
=LARGE(range, k)        → k-th largest value
=SMALL(range, k)        → k-th smallest value
=AVERAGEIF(range, criteria, avg_range)   → conditional average

Examples

Example 1Compare mean vs median for skewed salary data
=AVERAGE(C2:C8)     → 671,429   (pulled up by 12 LPA outlier)
=MEDIAN(C2:C8)     → 520,000   (middle value — more representative)
=STDEV(C2:C8)      → 281,457   (how spread out salaries are)
💡

When mean > median, data is right-skewed (a few high values pull the mean up). For salary reporting, always show both — mean for budgeting, median for typical employee comparison.

Example 2CORREL — does experience predict salary?
=CORREL(D2:D8, C2:C8)
OUTPUT
0.97   (very strong positive correlation)
💡

CORREL returns a value from -1 (perfect negative) to +1 (perfect positive). 0.97 means experience and salary are almost perfectly correlated in this dataset — as expected.

Example 3PERCENTILE and RANK — identify top performers
=PERCENTILE(E2:E8, 0.75)     → 4.7   (75th percentile rating)
=RANK(E2, E2:E8, 0)          → rank of Rahul rating (0 = descending)
=LARGE(C2:C8, 1)             → 1200000  (highest salary)
=LARGE(C2:C8, 3)             → 750000   (3rd highest salary)
💡

RANK with order=0 gives rank 1 to the highest value. order=1 gives rank 1 to the lowest. LARGE and SMALL let you extract the top/bottom N values without sorting the data.

📌 Key Points to Remember

  • ✓Use MEDIAN instead of AVERAGE for salary, price, and time data — outliers distort the mean
  • ✓STDEV (sample) vs STDEVP (population): use STDEV when your data is a sample of a larger dataset
  • ✓CORREL output: 0.7–1.0 = strong positive, 0.3–0.7 = moderate, below 0.3 = weak, negative = inverse relationship
  • ✓PERCENTILE(range, 0.9) finds the 90th percentile — useful for defining "top performer" thresholds
  • ✓AVERAGEIF works like SUMIF but averages instead of sums — same syntax, same power

🏢 Real-World Application

HR analytics teams use MEDIAN salary by department to identify pay equity issues. Sales managers use CORREL to validate whether call volume actually predicts revenue (if correlation is low, the metric is not a reliable KPI). Operations analysts use PERCENTILE to set SLA thresholds — e.g. "95% of tickets resolved within X days" — derived directly from the distribution of historical resolution times.

⚠️ Common Mistakes to Avoid

WRONGUsing AVERAGE for salary benchmarking when one executive salary inflates the result
FIXAlways show both AVERAGE and MEDIAN for compensation analysis. If they differ significantly, median is the more useful number for typical employee pay.
WRONGConfusing STDEV (sample) and STDEVP (population)
FIXIn business analysis, your data is almost always a sample. Use STDEV(). STDEVP is for when you have the entire population — e.g. all students in one school, not a sample of students.
WRONGInterpreting a weak CORREL as proof of no relationship
FIXCORREL measures linear relationships. Two variables can have a strong non-linear relationship (e.g. diminishing returns) but a low CORREL. Visualise the data in a scatter chart before concluding there is no relationship.
✏️Test Yourself

A dataset has salaries: 300000, 350000, 380000, 400000, 450000, 2500000. Which measure best represents a "typical" salary?

❓ Frequently Asked Questions

How do I calculate a weighted average in Excel?

Use SUMPRODUCT: =SUMPRODUCT(weights, values)/SUM(weights). For example, to weight exam scores by credit hours: =SUMPRODUCT(C2:C5, D2:D5)/SUM(C2:C5).

What is the difference between STDEV and STDEV.S?

They are identical — STDEV.S is the updated name in Excel 2010+. Use STDEV.S in new formulas for clarity. Both calculate sample standard deviation.

How do I calculate a 3-month moving average?

In cell D4 (third row of data): =AVERAGE(C2:C4). Copy down. This averages the current and two previous periods, smoothing out short-term fluctuations for trend analysis.

✏️ Practice Exercise

Using a dataset of 20 product prices from an e-commerce store, calculate: (1) Mean and median price — explain why they differ, (2) Standard deviation to understand price spread, (3) 25th, 50th, and 75th percentile prices, (4) Rank each product by price, (5) CORREL between price and units sold. Build a summary statistics table from these formulas.

← PreviousDynamic Arrays & Spill FunctionsNext →Financial Functions
🎓 Level Up Faster

Learn Excel with Live Trainer Guidance

These tutorials give you the foundations. Our live Excel course at EVIKA Academy, Noida teaches you to build real dashboards on actual business data — with a trainer who uses Excel professionally every day.