OmniDocStatistical Formulas in Excel: Median and More
Learn which Excel statistical formulas to use for median, average, and standard deviation, with a sample-versus-population check and worked examples.
A short excel statistical formulas list is useful, but the harder question is which formula tells the truth about your data. An average, a median and a standard deviation can describe the same column very differently. Picking the wrong one can make a report misleading without any error message.
This guide is for people who keep test scores, sales figures, survey answers or measurements in Excel and want to know what each common statistical function measures. It covers the core functions, a worked example you can reproduce, and the mistakes that trip up beginners. It stays on the formulas themselves, not on cleaning data or building full analyses.
You will also see how to check your results against a dataset with a known answer. If your numbers start in a PDF table, the last section explains how to move them into Excel first.
Which Excel statistical formula should you use? The short answer
Use AVERAGE for numbers that spread evenly, MEDIAN when a few extreme values could skew the result, and STDEV.S to measure spread in a sample.
| Your situation | What to do | Why |
|---|---|---|
| Values are spread fairly evenly with no extreme outliers | Use AVERAGE | It uses every value, so it reflects the whole column |
| A few very large or very small values are present | Use MEDIAN | It returns the midpoint, so outliers do not pull it far away |
| Your data is a sample from a larger group | Use STDEV.S | It divides by n minus 1, which corrects for sampling |
| Your list is the entire group you care about | Use STDEV.P | It divides by n, treating the list as the full population |
| You want the value that repeats most often | Use MODE.SNGL | It returns the repeated value, or #N/A if nothing repeats |
What do these formulas actually measure?
Statistical formulas answer two basic questions: where is the center of the data, and how spread out is it? Center measures include the average, the median and the mode. Spread measures include the standard deviation and the variance. Each formula answers one of these questions in its own way.
Center: average versus median
The average adds every value and divides by the count. The median is the middle value after sorting the list, or the mean of the two middle values when the count is even.
Take six values: 4, 8, 15, 16, 23 and 42. The average is 18, because the values add to 108 and divide by 6. The median is 15.5, the midpoint of 15 and 16. The 42 pulls the average upward, but it has no effect on the median beyond shifting the list's order.
In Excel, these are =AVERAGE(range) and =MEDIAN(range). When the two results are far apart, your data probably has outliers, and you should look at the list before deciding which number to report.
Spread: standard deviation
Standard deviation measures how far values typically sit from the mean. The formula squares each deviation from the mean, averages those squares, and takes the square root. Squaring keeps negative and positive deviations from canceling out.
Consider eight values: 2, 4, 4, 4, 5, 5, 7 and 9. The mean is 5. If you treat these eight values as the whole population, the standard deviation is exactly 2. If you treat them as a sample drawn from a larger group, the standard deviation is about 2.14. Same numbers, different formula, different answer.
Easily confused points
- Sample versus population. STDEV.S divides by n minus 1 and is the usual choice when your data is a sample. STDEV.P divides by n. Older Excel versions call these STDEV and STDEVP, and you will see both names in old workbooks.
- Blank cells and text. AVERAGE, MEDIAN and STDEV skip blank cells and text values in a range. A zero, however, is a real number and gets counted. If a figure looks wrong, check whether some numbers were stored as text, which often shows up as values aligned to the left.
- No repeated values. MODE.SNGL returns #N/A when no value appears more than once. That result means the data has no mode, not that the formula is broken.
📊 At a glance
| Function | What it tells you | Notes |
|---|---|---|
| AVERAGE | Arithmetic mean | Sensitive to outliers |
| MEDIAN | Middle value | Resistant to outliers |
| MODE.SNGL | Most repeated value | Returns #N/A if nothing repeats |
| MIN and MAX | Lowest and highest values | Useful for checking range before other calculations |
| STDEV.S | Spread for a sample | Divides by n minus 1 |
| STDEV.P | Spread for a full population | Divides by n |
| QUARTILE.INC | Value at a chosen quartile | Quartile 2 equals the median |
Putting it to use
Work through a short example with twelve test scores in column B, rows 2 to 13. The steps below work on any similar list.
- Enter your numbers in one column, such as B2 to B13, with no headers inside the range.
- In an empty cell, type =MEDIAN(B2:B13) and press Enter to get the median.
- In the cell next to it, type =AVERAGE(B2:B13). If the two results differ a lot, scan the list for values far from the rest.
- For spread, type =STDEV.S(B2:B13) if the twelve scores are a sample of a larger class. Use =STDEV.P(B2:B13) only when those twelve scores are everyone you are measuring.
- Test the formulas on a known dataset. Enter 2, 4, 4, 4, 5, 5, 7 and 9 in a spare column. STDEV.P should return 2, and STDEV.S should return about 2.14. If your results differ, check the range you selected.
- If the numbers begin in a PDF table, open the file in the desktop client, available at download.omnidoc.top. The PDF editor can export content to Excel. For a scanned PDF, run OCR first so the digits become text. OCR can misread characters, so compare the exported figures with the source before you calculate.
❓ FAQ
1. Does MEDIAN ignore blank cells?
Yes. Blank cells in the range are skipped, but a cell containing 0 is counted as a value. If a blank should mean "no data," leave the cell empty rather than typing a zero.
2. Why does my MODE.SNGL formula show #N/A?
The formula returns #N/A when no value appears twice or more in the range. Check for typos or numbers stored as text, which Excel treats as separate values.
3. Do these formulas include hidden rows?
Yes. AVERAGE, MEDIAN and STDEV include hidden rows in the range. If you want only visible rows, use SUBTOTAL or AGGREGATE with a function code that ignores hidden values.
4. What is the difference between STDEV and STDEV.S?
They are the same calculation for a sample. STDEV is the older name, and STDEV.S is the newer name that makes the sample assumption clear. Use STDEV.S in new workbooks.
5. Should I report the average or the median for income data?
Income data is often skewed, with a few very high values. In that case the median usually describes a typical value better than the average, though you may report both and explain the gap.
📝 Summary
An excel statistical formulas list works well when each function is matched to a specific question about your data. Use AVERAGE and MEDIAN for center, STDEV.S or STDEV.P for spread, and MODE.SNGL for repeated values. Test each formula on a small dataset with a known answer before you rely on it.
