# How to Calculate the Median in Excel Using Formulas?

Are you looking for an easy way to find the **median** value in a dataset using **Microsoft Excel**? The median is a useful **statistical measure** that represents the middle value in a set of numbers. Excel provides a simple **MEDIAN formula** to quickly calculate this value. In this article, we’ll explain what the median is, show you how to use the **MEDIAN function** in Excel with examples, and cover some tips and best practices.

## What is the Median?

The median is the middle number in a sorted list of values. It splits the dataset into two equal halves, with half the numbers below the median and half above. The median is less affected by outliers and skewed data than the **mean (average)**, so it is often a better measure of the central tendency of the data.

To find the median manually:

- Arrange the numbers in ascending or descending order
- If there is an odd number of values, the median is the middle value
- If there is an even number of values, the median is the average of the two middle values

For example, in the dataset {1, 3, 3, 6, 7, 8, 9}, the median value is 6. In {1, 2, 3, 4, 5, 6}, the median is (3 + 4) / 2 = 3.5.

While you can calculate the median by hand for small datasets, it becomes tedious and impractical for larger ones. This is where the MEDIAN formula in Excel comes in handy.

## Using the MEDIAN Function in Excel

The **MEDIAN function** in Excel returns the median (middle) value in a set of numbers. It takes the following arguments:

=MEDIAN(number1, [number2], …)

- number1 – The first number or cell reference of the dataset. This is required.
- number2, … – Additional numbers or cell references to include in the dataset. These are optional.

The MEDIAN function ignores empty cells, text values, and logical values like TRUE or FALSE. If there are no numbers in the provided values, it will return the #NUM! error.

### Example 1: MEDIAN of Numbers

To find the median of a explicit set of numbers, you can provide them directly as arguments to the MEDIAN function:

=MEDIAN(1, 3, 3, 6, 7, 8, 9)

This will return 6, since it is the middle value in the provided numbers.

### Example 2: MEDIAN of a Cell Range

More often, your data will be in cells in a worksheet. To find the median, simply provide the cell range as the argument:

=MEDIAN(A1:A10)

This will calculate the median value of the numbers in cells A1 through A10.

### Example 3: MEDIAN with Non-Adjacent Ranges

You can also find the median of numbers in non-adjacent cells or ranges by separating the arguments with commas:

=MEDIAN(A1:A5, C1:C5, E1)

This finds the median of the values in A1:A5, C1:C5, and cell E1.

## Tips for Using MEDIAN in Excel

Here are a few tips and things to keep in mind when using the MEDIAN function:

- Make sure your data is numerical. The MEDIAN function ignores text values.
- Be careful when using formatted numbers (like percentages or currency). Excel uses the underlying numeric value, not the displayed value, for calculations.
- If you need to exclude certain values, you can filter your data first before using MEDIAN.
- Remember that MEDIAN only returns a single value, not the location. If you need to find the position of the median value, you can use the
**MATCH**function together with MEDIAN.

## MEDIAN vs AVERAGE

The **AVERAGE** function is another commonly used statistical function in Excel. While both MEDIAN and AVERAGE measure the central tendency, they calculate different values:

**AVERAGE**calculates the arithmetic mean, the sum of all values divided by the count of numbers.**MEDIAN**finds the middle value that splits the dataset into two equal parts.

AVERAGE is more affected by outliers and extreme values, while MEDIAN is more robust. For example, consider the dataset {1, 2, 3, 4, 100}:

- AVERAGE = (1+2+3+4+100) / 5 = 22
- MEDIAN = 3

In this case, the very high value of 100 skews the AVERAGE much higher than most of the values, while the MEDIAN remains unaffected.

In general, MEDIAN is a better measure when:

- Your data is skewed (asymmetrical) and contains outliers
- You want a value that is actually present in the dataset
- You have a small sample size

AVERAGE is better when:

- Your data is symmetrical (follows a normal distribution)
- You have a very large dataset where the impact of outliers is minimized

## Using MEDIAN with Other Excel Functions

The MEDIAN function can be combined with other Excel functions to perform more advanced analyses. Here are a few examples:

### MEDIAN with IF

You can use the **IF function** to conditionally calculate the median based on some criteria. For example, to find the median price of products where the quantity sold is greater than 100:

=MEDIAN(IF(B2:B100>100, C2:C100))

This checks if the quantity in column B is greater than 100, and if so, includes the corresponding price from column C in the MEDIAN calculation.

### MEDIAN with FREQUENCY and MODE.SNGL

To find the **mode** (most frequently occurring value) of a dataset manually, you can use MEDIAN together with the **FREQUENCY** and **MODE.SNGL** functions.

First, use FREQUENCY to count the occurrences of each value:

`=FREQUENCY(data_range, data_range)`

Then, find the maximum frequency with:

`=MODE.SNGL(frequency_range)`

Finally, use MEDIAN to find the corresponding value:

`=MEDIAN(IF(frequency_range=max_frequency, data_range))`

This checks which frequencies match the maximum, and returns the median of the corresponding values.

### MEDIAN with SUMPRODUCT

You can use **SUMPRODUCT** to count the number of values above or below the median. For example:

`=SUMPRODUCT(--(A1:A100>MEDIAN(A1:A100)))`

This counts the number of values in A1:A100 that are greater than the median.

`=SUMPRODUCT(--(A1:A100<MEDIAN(A1:A100))) `

This counts the number of values less than the median.

## Final Thoughts

The **MEDIAN function** is a quick and easy way to find the **middle value** in a dataset using Excel. It’s a robust measure of **central tendency** that is less affected by outliers and skewed data than the **mean**. Simply provide your numbers or cell references as arguments to MEDIAN, and Excel will do the rest. Whether you have a small set of numbers or a large dataset across multiple worksheet ranges, the MEDIAN formula has you covered.

By understanding how to effectively use **MEDIAN in Excel**, you can better summarize and analyze your data. Combine it with other functions like **IF**, **FREQUENCY**, and **SUMPRODUCT** to gain even more insights.

## FAQs

### How do I calculate median in Excel?

To calculate the median in Excel, use the MEDIAN function. You can provide a range of cells or individual numbers as arguments, like =MEDIAN(A1:A100) or =MEDIAN(1, 3, 3, 6, 7, 8, 9). Excel will automatically find the middle value in the dataset.

### What is the formula for median in Excel?

The formula for median in Excel is =MEDIAN(number1, [number2], …). number1 is the first number or cell reference of the dataset, and number2 and beyond are optional additional numbers or cell references to include.

### What is the difference between median and average in Excel?

The median is the middle value in a dataset that splits it into two equal halves, while the average (or mean) is the sum of all values divided by the count of numbers. The median is less affected by outliers and skewed data than the average.

### How do you find the median of a set of numbers in Excel?

To find the median of a set of numbers in Excel, use the MEDIAN function and provide the numbers as arguments, like =MEDIAN(1, 3, 3, 6, 7, 8, 9). You can also provide a range of cells containing the numbers, such as =MEDIAN(A1:A100).

### How do I ignore text values when calculating median in Excel?

The MEDIAN function automatically ignores any text values, logical values (like TRUE/FALSE), and empty cells in the provided numbers or ranges. It only considers the numeric values when calculating the median.

### Can you use MEDIAN with filtered data in Excel?

Yes, you can use the MEDIAN function with filtered data in Excel. Apply the filter to your dataset first, then use the MEDIAN function as usual. It will calculate the median based only on the visible (filtered) cells.

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.