How to Calculate the Median in Excel Using Formulas?

Sharing is caring!

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:

1. Arrange the numbers in ascending or descending order
2. If there is an odd number of values, the median is the middle value
3. 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

Vaishvi Desai

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.