Here are the best information abouthow to calculate medianvoted by readers and compiled and edited by our team, let’s find out

The tutorial shows how to use the MEDIAN function to calculate a median of numeric values in Excel.

The median is one of the three main measures of central tendency, which is commonly used in statistics for finding the center of a data sample or population, e.g. for calculating a typical salary, household income, home price, real-estate tax, etc. In this tutorial, you will learn the general concept of the median, in what way it is different from arithmetic mean, and how to calculate it in Excel.

- What is median?
- Excel MEDIAN function
- How to find median in Excel
- Excel MEDIAN formula
- Median IF formula
- Median IFS formula with multiple conditions

Table of Contents

## What is median?

In simple terms, the **median** is the middle value in a group of numbers, separating the higher half of values from the lower half. More technically, it is the center element of the data set arranged in order of magnitude.

In a data set with an odd number of values, the median is the middle element. If there are an even number of values, the median is the average of the middle two.

For example, in the group of values {1, 2, 3, 4, 7} the median is 3. In the dataset {1, 2, 2, 3, 4, 7} the median is 2.5.

Compared to the arithmetic mean, the median is less susceptible to outliers (extremely high or low values) and therefore it is the preferred measures of central tendency for an asymmetrical distribution. A classic example is a median salary, which gives a better idea of how much people typically earn than an average salary because the latter may be skewed by a small number of abnormally high or low salaries. For more information, please see Mean vs. median: which is better?

## Excel MEDIAN function

Microsoft Excel provides a special function to find a median of numeric values. Its syntax is as follows:

Where **Number1, number2, …** are numeric values for which you want to calculate the median. These can be numbers, dates, named ranges, arrays, or references to cells containing numbers. **Number1** is required, subsequent numbers are optional.

In Excel 2007 and higher, the MEDIAN function accepts up to 255 arguments; in Excel 2003 and earlier you can only supply up to 30 arguments.

### 4 facts you should know about Excel Median

- When the total number of values is odd, the function returns the middle number in the data set. When the total number of values is even, it returns an average of the two middle numbers.
- Cells with zero values (0) are included in calculations.
- Empty cells as well as cells containing text and logical values are ignored.
- The logical values TRUE and FALSE typed directly in formula are counted. For example, the formula MEDIAN(FALSE, TRUE, 2, 3, 4) returns 2, which is the median of the numbers {0, 1, 2, 3, 4}.

## How to calculate median in Excel – formula examples

MEDIAN is one of the most straightforward and easy-to-use functions in Excel. However, there are still some tricks, not obvious to beginners. Say, how do you calculate a median based on one or more conditions? The answer is in one of the following examples.

### Excel MEDIAN formula

For starters, let’s see how to use the classic MEDIAN formula in Excel to find the middle value in a set of numbers. In a sample sales report (please see the screenshot below), supposing you want to find the median of numbers in cells C2:C8. The formula would be as simple as this:

=MEDIAN(C2:C8)

As shown in the screenshot above, the formula works for numbers and dates equally well since in terms of Excel dates are also numbers.

### Excel MEDIAN IF formula with one criterion

Regrettably, Microsoft Excel does not provide any special function to calculate a median based on a condition like it does for the arithmetic mean (AVERAGEIF and AVERAGEIFS functions). Luckily, you can easily build your own MEDIAN IF formula in this way:

In our sample table, to find a median amount for a specific item, input the item name in some cell, say E2, and use the following formula to get the median based on that condition:

=MEDIAN(IF($A$2:$A$10=$E2, $C$2:$C$10))

The formula tells Excel to calculate only those numbers in column C (Amount) for which a value in column A (Item) matches the value in cell E2.

Please pay attention that we use the $ symbol to create absolute cell references. It is especially important if you intend to copy your Median If formula to other cells.

Finally, since you want to check every value in the specified range, make it an array formula by pressing Ctrl + Shift + Enter. If done correctly, Excel will encapsulate the formula in curly braces like shown in the screenshot below.

In dynamic array Excel (365 and 2021) it also works as a regular formula.

### Excel Median IFS formula with multiple criteria

Taking the previous example further, let’s add one more column (Status) to the table, and then find a median amount for each item, but count only orders with the specified status. In other words, we will be calculating median based on two conditions – item name and order status. To express **multiple criteria**, use two or more nested IF functions, like this:

With criteria1 (Item) in cell F2 and criteria2 (Status) in cell G2, our formula takes the following shape:

=MEDIAN(IF($A$2:$A$10=$F2, IF($D$2:$D$10=$G2,$C$2:$C$10)))

Since it’s an array formula, remember to hit Ctrl + Shift + Enter to complete it correctly. If all done properly, you will get a result similar to this:

This is how you calculate median in Excel. To have a closer look at the formulas discussed in this tutorial, you are welcome to download our sample workbook below. I thank you for reading and hope to see you on our blog next week!

## Practice workbook

MEDIAN formula Excel – examples (.xlsx file)

## You may also be interested in

- Mean, median and mode in Excel
- How to calculate average (mean) in Excel
- Calculating weighted average in Excel
- Calculating moving average in Excel

## Top 13 how to calculate median edit by Top Q&A

### Median Of Even Numbers | How To Find Median – DK Find Out!

**Author:**dkfindout.com**Published Date:**11/21/2022**Review:**4.99 (627 vote)**Summary:**When there is an even number of values in a set of data, there is no single middle value. That makes it a little more difficult to find the median.

### How to Find the Median of Grouped Data (With Examples)

**Author:**statology.org**Published Date:**11/13/2022**Review:**4.61 (533 vote)**Summary:**Median = L + W[(N/2 – C) / F]; Median = 71 + 9[(40/2 – 12) / 15]; Median = 75.8. We estimate …

### Median Concepts and Definitions

**Author:**stats.mom.gov.sg**Published Date:**04/16/2022**Review:**4.54 (412 vote)**Summary:**Else, if the number of observations is even, then the median is the simple average of the middle two numbers. In calculation, the median is the simple average …

Top 10+ how to get bigger lips naturally

### How to Find the Median Value

**Author:**mathsisfun.com**Published Date:**12/12/2022**Review:**4.24 (351 vote)**Summary:**To find the Median, place the numbers in value order and find the middle. Example: find the Median of 12, 3 and 5. Put them in order: 3, 5, 12. The middle is …

### How to find the mean, median, mode and range

**Author:**bbc.co.uk**Published Date:**05/16/2022**Review:**4.15 (559 vote)**Summary:**Mode, median and mean are three types of average. Find out how to calculate them and the range of numbers in this KS2 Primary Maths guide.

### Median: What It Is and How to Calculate It, With Examples

**Author:**investopedia.com**Published Date:**02/24/2023**Review:**3.8 (410 vote)**Summary:**The median is the middle value in a set of data. First, organize and order the data from smallest to largest. To find the midpoint value, divide …

How long does it take a flu shot to work

### How to find the median

**Author:**ionos.com**Published Date:**08/04/2022**Review:**3.59 (488 vote)**Summary:**Do you want to calculate the median of a large data set, but don’t know how? Read on to learn how you can find the median.

### Median Calculator: Calculate Median Online & Free!

**Author:**mediancalculator.com**Published Date:**02/18/2023**Review:**3.42 (232 vote)**Summary:**To begin with, we will arrange all the numbers in ascending order (from the smallest to the largest). It will be {1, 5, 6, 8, 9}. The number that appears in the …

### Python statistics.median() Method – W3Schools

**Author:**w3schools.com**Published Date:**12/17/2022**Review:**3.33 (251 vote)**Summary:**Calculate the median (middle value) of the given data: # Import statistics Library import statistics # Calculate middle values print(statistics.median([1, …

Top 20+ how soon can you refinance a mortgage

### What is Median in Statistics (Formula, Calculation, Examples & Properties)

**Author:**geeksforgeeks.org**Published Date:**04/04/2022**Review:**3.04 (572 vote)**Summary:**How to calculate the Median of grouped data: How to find a median of an unsorted array? Given an unsorted array arr[] of size N, …

### How to Calculate the Median in SPSS

**Author:**ezspss.com**Published Date:**08/16/2022**Review:**2.81 (142 vote)**Summary:**This quick tutorial will show how to calculate the median of a set of data in SPSS. Quick Steps. Click Analyze -> Descriptive Statistics -> Frequencies; Move …**Matching search results:**MEDIAN is one of the most straightforward and easy-to-use functions in Excel. However, there are still some tricks, not obvious to beginners. Say, how do you calculate a median based on one or more conditions? The answer is in one of the following …

### 4.4 Measures of central tendency 4.4.2 Calculating the median

**Author:**www150.statcan.gc.ca**Published Date:**08/29/2022**Review:**2.79 (69 vote)**Summary:**For a small data set, you first count the number of data points (n) and arrange the data points in increasing order. If the number of data …**Matching search results:**MEDIAN is one of the most straightforward and easy-to-use functions in Excel. However, there are still some tricks, not obvious to beginners. Say, how do you calculate a median based on one or more conditions? The answer is in one of the following …

### How Do You Find the Median of a Data Set?

**Author:**virtualnerd.com**Published Date:**03/30/2022**Review:**2.63 (127 vote)**Summary:**In this tutorial, see how to find the median of a data set, and see what to do if there are two middle values! Keywords: problem; solve; median; order; order …**Matching search results:**MEDIAN is one of the most straightforward and easy-to-use functions in Excel. However, there are still some tricks, not obvious to beginners. Say, how do you calculate a median based on one or more conditions? The answer is in one of the following …