## What You'll Learn / Key Take-Aways

- How to use core Excel statistical functions: COUNT, AVERAGE, MEDIAN, MODE, MAX, MIN, PERCENTILE, STDEV and VAR.

- How to reference ranges across sheets and why Excel adds the sheet name automatically.

- How to specify percentiles using a K value between 0 and 1 (for example 0.25 for the 25th percentile).

- Practical notes: MODE can take longer to compute, variance is the square of standard deviation, and choose the correct STDEV variant for sample vs population.

## Overview: Why these functions matter

Statistical functions give you quick summaries of the distribution and central tendency of your data. In practice you will use them for quality checks, dashboards, and exploratory analysis. The syntax is simple. Most functions take a single reference array argument. Percentile functions add a second argument, K, that specifies which percentile to return as a number between 0 and 1.

## The basic functions and their usage

Here are the common functions and the core pattern you will use in Excel.

- COUNT(range)

- Returns the number of numeric cells in range. Use this to get sample size.

- AVERAGE(range)

- Returns the arithmetic mean of numeric values in range.

- MEDIAN(range)

- Returns the middle value in the sorted list of numbers.

- MODE(range)

- Returns the most frequently occurring value. Note: MODE can take longer to compute on large data sets.

- MAX(range) and MIN(range)

- Return the largest and smallest values respectively.

- PERCENTILE(range, k)

- Returns the value at the k-th percentile. Use 0.75 for the 75th percentile, 0.25 for the 25th.

- STDEV(range)

- Returns standard deviation. In newer Excel versions prefer STDEV.S for a sample or STDEV.P for a population.

- VAR(range)

- Returns variance. Conceptually this is the square of standard deviation.

### Example pattern

In most cases the formula just points at an array. For example, to calculate the median of salaries in column E on a sheet called Salary Data you would use:

=MEDIAN('Salary Data'!E:E)

To return the 75th percentile of the same column:

=PERCENTILE('Salary Data'!E:E, 0.75)

Excel automatically appends the sheet name when you select ranges across tabs, so you rarely need to type that part manually.

## Walkthrough: using these functions on salary data

Imagine you have five years of major league baseball salary data with a Salary column in column E on a sheet named Salary Data. Here are practical steps to replicate what we covered.

1. Sample size

- Type =COUNT(

- Click the Salary Data sheet and select column E

- Close parentheses and press Enter

2. Average salary

- =AVERAGE('Salary Data'!E:E)

3. Median salary

- =MEDIAN('Salary Data'!E:E)

4. Mode salary

- =MODE('Salary Data'!E:E)

- Note: on large datasets MODE may pause while Excel calculates.

5. Max and Min

- =MAX('Salary Data'!E:E)

- =MIN('Salary Data'!E:E)

6. Percentiles

- 75th percentile: =PERCENTILE('Salary Data'!E:E, 0.75)

- 25th percentile: =PERCENTILE('Salary Data'!E:E, 0.25)

7. Standard deviation and variance

- =STDEV('Salary Data'!E:E) or =STDEV.S('Salary Data'!E:E) for sample

- =VAR('Salary Data'!E:E)

## Notes on interpretation

- Median vs Average. Average is sensitive to extreme values. Median gives the middle value and is more robust if your data has outliers. In salary data you will often see large differences between average and median because top salaries inflate the mean.

- Percentiles. A percentile tells you where a value sits within the distribution. The 25th percentile is the value below which 25 percent of observations fall.

- Standard deviation and variance. Standard deviation describes the spread around the mean. Variance is standard deviation squared. Variance can be a very large number and is less immediately interpretable than standard deviation.

## Additional Tips, Pitfalls to Avoid & Pro Advice

- When referencing entire columns use the whole-column reference like E:E only if you are okay including headers or blanks. For mixed data types or headers, consider E2:E1000 or an Excel Table to avoid including text in numeric calculations.

- MODE can be slower than other functions on large datasets. If it pauses, give Excel a few seconds. If you need the most frequent value for multiple groups consider pivot tables or COUNTIF with MAX.

- Use STDEV.S for a sample and STDEV.P for a population. The older STDEV function maps to STDEV.S in recent Excel versions but check your Excel help if results look off.

- Percentile function variants. In modern Excel you also have PERCENTILE.INC and PERCENTILE.EXC which differ slightly in how they interpolate. PERCENTILE is usually an alias for PERCENTILE.INC.

- Cross-sheet references. Excel adds the sheet name automatically when you click a different tab. You do not need to type sheet names manually unless you prefer to.

## FAQ

Q: How do I calculate the number of records in a column that contains blanks or text?

A: Use COUNT to count numeric cells only. If you want to count all nonblank rows use COUNTA(range). For conditional counting use COUNTIF or COUNTIFS.

Q: What value should I use for the percentile function to get the median?

A: Use 0.5 as the K argument. For example, =PERCENTILE(range, 0.5) returns the median.

Q: Why is my MODE calculation taking a long time?

A: MODE can be computationally expensive on large datasets because it searches for frequency of each value. If you have many unique values, consider using a pivot table or COUNTIF + FILTER to compute the top occurrence more efficiently.

Q: When should I use STDEV.S versus STDEV.P?

A: Use STDEV.S when your data is a sample of a larger population. Use STDEV.P when your data includes the entire population you care about.

Q: My average is much higher than my median. What does that mean?

A: That usually indicates a right-skewed distribution where high outliers pull the mean upward. In salaries, a few very large contracts can raise the average while the median stays lower.

## Summary of Key Points / Take-Home Messages

- Most Excel statistical functions use a single reference range as their argument.

- Percentiles require a K value between 0 and 1; 0.25 = 25th percentile, 0.75 = 75th.

- Use median to guard against outliers, average to report overall mean, and MODE to find the most common value.

- STDEV describes spread; VAR is its square and can be less intuitive.

- Watch out for performance on MODE and choose STDEV.S or STDEV.P appropriately.