How To Calculate Mean And Median In Excel

7 min read

Calculating the mean and median in Excel is one of the first statistical skills any student, analyst, or business professional should master, because these two numbers describe the "center" of a dataset in different but complementary ways. Worth adding: excel makes the process remarkably simple through the AVERAGE and MEDIAN functions, yet many users still calculate them incorrectly by typing numbers by hand or by ignoring blank cells, text entries, and outliers that quietly distort their results. This guide walks you through every method step by step, explains the logic behind each function, and shows you when to trust the mean and when the median tells a more honest story.

Understanding Mean and Median Before You Type a Formula

Before touching a formula, it helps to know exactly what you are asking Excel to do.

The mean (often called the arithmetic average) is the sum of all values divided by the number of values. Consider this: if you have five test scores — 70, 75, 80, 85, and 90 — the mean is 400 ÷ 5 = 80. The mean uses every single data point, which makes it sensitive to extreme values.

The median is the middle value when all numbers are arranged from smallest to largest. Here's the thing — in the same dataset, the median is 80 as well, because it sits in the third position. If the dataset had an even number of values, the median would be the average of the two middle numbers.

The critical difference appears when your data is skewed. Imagine the same five scores plus one outlier of 1000. The mean jumps to roughly 233, while the median stays at 82.5. That single example explains why salary reports, house prices, and exam results are often described using the median rather than the mean.

Most guides skip this. Don't.

Setting Up Your Data Correctly

Excel's statistical functions are only as reliable as the range you feed them. Follow these preparation rules:

  • Keep one variable per column. Place all the numbers you want to analyze in a single continuous column or row.
  • Leave gaps out of the range. Blank cells inside a range are ignored by AVERAGE and MEDIAN, but blank rows that split your data can cause you to select the wrong area.
  • Watch for numbers stored as text. Values imported from other systems often arrive as text, and Excel will silently skip them. A quick check: text-based numbers usually align to the left of the cell, while true numbers align to the right.
  • Remove or isolate totals. If your column ends with a "Total" row, including it in your range will double-count your data.

A clean layout might look like this, with scores in column B from row 2 to row 11:

Student Score
Aisyah 78
Bima 85
Chen 92
Dewi 67
Eko 74

How to Calculate the Mean in Excel Using AVERAGE

The fastest route to the mean is the AVERAGE function.

  1. Click an empty cell where you want the result to appear.
  2. Type =AVERAGE( and then select your range with the mouse, or type it manually.
  3. Close the parenthesis and press Enter.

Your formula will look like this:

=AVERAGE(B2:B11)

Excel adds every numeric value in that range and divides by the count of numeric cells. Blank cells, text, and logical values inside the range are ignored, which is usually exactly what you want.

Averaging Non-Adjacent Ranges

You are not limited to one block of cells. Separate multiple ranges with commas:

=AVERAGE(B2:B11, D2:D11, F2:F11)

This is useful when your data is split across several columns, such as scores from three different semesters.

Averaging with Conditions: AVERAGEIF and AVERAGEIFS

Sometimes you need the mean of only part of your data — for example, the average score of students in the "Science" class only.

=AVERAGEIF(A2:A11, "Science", B2:B11)

For two or more conditions, use AVERAGEIFS, which places the average range first:

=AVERAGEIFS(B2:B11, A2:A11, "Science", C2:C11, ">70")

Calculating a Weighted Mean

A plain average treats every value equally, but grades and prices often carry different weights. Use SUMPRODUCT divided by the sum of the weights:

=SUMPRODUCT(B2:B11, C2:C11)/SUM(C2:C11)

Here column B holds the values and column C holds the weights. This single formula replaces a long manual multiplication table Small thing, real impact. And it works..

How to Calculate the Median in Excel Using MEDIAN

The MEDIAN function works almost identically to AVERAGE, but it returns the middle value of the sorted dataset Most people skip this — try not to. But it adds up..

  1. Select an empty cell.
  2. Type =MEDIAN( and highlight your range.
  3. Press Enter.

=MEDIAN(B2:B11)

Excel sorts the numbers internally, finds the center, and returns it. You never need to sort your column yourself — the function handles that automatically, and your original data order stays untouched That alone is useful..

Median with Conditions

Excel has no built-in MEDIANIF, but you can achieve the same result with an array formula. In modern versions of Excel (Microsoft 365 and Excel 2021), simply type:

=MEDIAN(IF(A2:A11="Science", B2:B11))

In older versions, enter the formula and press Ctrl + Shift + Enter so Excel wraps it in curly braces. The IF statement passes only the matching values to MEDIAN and filters out the rest.

A Quick Trick Without Any Formula

If you only need a fast glance at the mean and median, select your range and look at the status bar at the bottom of the Excel window. Excel displays Average, Count, and Sum by default. Here's the thing — right-click the status bar to enable Median and other statistics. This is perfect for a quick sanity check before you commit to a formula.

Short version: it depends. Long version — keep reading Simple, but easy to overlook..

Mean vs Median: Choosing the Right Measure

Knowing how to calculate both is only half the skill. Knowing which one to report is the other half.

  • Use the mean when your data is roughly symmetrical and you want to use every value, such as average daily temperature or average order value.
  • Use the median when your data contains outliers or is heavily skewed, such as household income, house prices, or response times with a few extreme delays.
  • Report both when you want to demonstrate the shape of the distribution. A large gap between mean and median is a strong signal that outliers are present.

A practical rule of thumb: if the mean is much higher than the median, your data has a long tail on the right. If the mean is much lower, the tail runs to the left.

Common Errors and How to Fix Them

#DIV/0! appears when the range contains no numeric values at all. Check that your numbers are not stored as text.

#VALUE! usually means you accidentally included a cell reference that Excel cannot interpret, such as a range from a closed workbook.

Wrong results from hidden rows. AVERAGE and MEDIAN include values in hidden rows. If you need to

exclude hidden rows, you'll need a more advanced formula or to unhide them first.

Practical Example: Salary Data

Imagine a small company with ten employees. Their monthly salaries are: $3,500, $3,800, $4,000, $4,200, $4,500, $4,800, $5,000, $5,200, $5,500, and $25,000 (the CEO) Still holds up..

  • The mean salary is $5,950. This figure is heavily skewed by the CEO's income and doesn't represent a typical employee.
  • The median salary is $4,650. This value, sitting between $4,500 and $4,800, is a much better indicator of what a normal employee earns.

In this case, reporting only the mean would be misleading. The median provides a more honest picture of the central tendency for the majority of the workforce.

Advanced Tip: MEDIAN with Multiple Criteria

For more complex scenarios, such as finding the median sales for a specific product in a specific region, you can extend the array formula logic. Using the same logic as the single-criteria example, you can incorporate multiple IF conditions with multiplication.

To give you an idea, to find the median sales for "Product A" in the "West" region:

=MEDIAN(IF((A2:A100="Product A")*(C2:C100="West"), B2:B100))

In this formula, the multiplication acts as an AND operator. Only when both conditions are true does the corresponding sales value get passed to the MEDIAN function The details matter here. Which is the point..

Conclusion

Mastering the MEDIAN function in Excel is a crucial skill for accurate data analysis. By understanding when to use each, and leveraging array formulas for conditional analysis, you can move beyond simple calculations and gain deeper, more reliable insights from your data. While the AVERAGE function is useful for symmetrical data, the MEDIAN function provides a dependable measure of center that is resistant to outliers and skewed distributions. Remember to check your data for text and hidden rows, and consider reporting both mean and median to fully understand your dataset's shape Less friction, more output..

New Content

Hot Right Now

See Where It Goes

In the Same Vein

Thank you for reading about How To Calculate Mean And Median In Excel. We hope the information has been useful. Feel free to contact us if you have any questions. See you next time — don't forget to bookmark!
⌂ Back to Home