How to Use the MEDIAN Formula in Excel: Functions, Examples, and Writing Steps
Home >> Excel Tutorials from Compute Expert >> Excel Formulas List >> How to Use the MEDIAN Formula in Excel: Functions, Examples, and Writing Steps
In this tutorial, you will learn how to use the MEDIAN formula in excel completely.
When working with numbers in excel, we might sometimes need to find the median (the middle value) of these numbers. If we know the way to use the MEDIAN function in excel, then we can get that median result easily.
Want to know more about MEDIAN and master the way to use it in excel? Read this tutorial until its last part!
Disclaimer: This post may contain affiliate links from which we earn commission from qualifying purchases/actions at no additional cost for you. Learn more
Want to work faster and easier in Excel? Install and use Excel add-ins! Read this article to know the best Excel add-ins to use according to us!
Table of Contents:
What is the MEDIAN Formula in Excel?
MEDIAN is an excel function that can help us to get the median of our numbers.MEDIAN Function in Excel
We can use MEDIAN to calculate the median of the numbers we input into it.MEDIAN Result
The MEDIAN result is a number that represents the median of the numbers we input into it.Excel Version from Which We Can Start Using MEDIAN
We can start using MEDIAN in excel since excel 2003.The Way to Write It and Its Inputs
Here is the general way to write MEDIAN in excel.
= MEDIAN ( number1 , [ number2 ] , … )
When writing the MEDIAN function in excel, we just need to input the numbers we want to get our median from.
We can input the numbers by typing them directly or by using cell or cell range coordinates. If we give more than one input to MEDIAN, we need to separate them with comma signs ( , ).
Usually, though, we only input one cell range which contains all numbers we want to get our median from to MEDIAN.
Example of Its Usage and Input
Here is a usage and result example of MEDIAN in excel.
As you can see in the example, we can get the median of our numbers by using the MEDIAN formula. Just input all the numbers we want to calculate the median from to immediately get the median.
Like a normal median calculation, if our number of numbers is odd, MEDIAN will give us their middle number. If the number of numbers is even, MEDIAN will average our two middle numbers to give us its result.
MEDIAN ignores texts, logical values, or empty cells in its operation, as you can see in the example’s third row.
Writing Steps
After we have discussed its writing form, inputs, and example, now let’s discuss the MEDIAN writing steps in excel. As its inputs are only numbers we want to get our median from, its writing steps should be easy to follow!-
Type an equal sign ( = ) in the cell where you want to put your numbers median
-
Type MEDIAN (can be with small and large letters) and an open bracket sign after =
-
Input all the numbers you want to get your median from. You can input the numbers by typing them directly or by using cell or cell range coordinates. Don’t forget to type comma signs between your inputs if you give more than one inputs to your MEDIAN
-
Type a close bracket sign after you have finished inputting all the numbers
- Press Enter
-
Done!
Find the Median from Numbers that Meet Our Criteria: MEDIAN IF
What if we have a criterion for the data entries which numbers we want to find our median from? Is there any way to calculate this in excel?Unfortunately, unlike AVERAGE (AVERAGEIF and AVERAGEIFS) or SUM (SUMIF and SUMIFS), we don’t have a special function to calculate median with criteria in excel. One alternative method we can use, though, is by combining MEDIAN with IF.
Here is the general writing form of the two formulas combination for the purpose.
{ = MEDIAN ( IF ( data_range = criterion , numbers_range ) ) }
Why there are curly brackets in the formula writing? That is because we use an array formula form here.
As we use IF to process a cell range here, we get our result in an array. As such, we need to use an array formula form to process it smoothly. To get the array formula form for our MEDIAN IF, we must press Ctrl + Shift + Enter (not just Enter like a normal formula) after we write the formula.
We use IF here to select the data entries which fulfill our criterion. We do the selection by inputting a logic condition where the data range in the data entries equals our criterion. This data range contains the data in our data entries that we want to evaluate with our criterion.
We input the numbers range from our data entries as the IF true result. The data range and numbers range here should be parallel and have the same size (usually columns/rows from the same data table).
Through the IF process, only the data entries which data fulfill our criterion will contribute their number for our median calculation!
After we get the numbers that have passed the selection from IF, we get their median using MEDIAN.
By doing that, we will get the median from the numbers whose data entries fulfill our criterion!
Here is the implementation example of the MEDIAN and IF formulas combination in excel.
In the example, we want to calculate the median of group B numbers. As we have a criterion here, we use MEDIAN IF to get the median for us.
As our criterion is group B, we write the group column equals to “B” in our IF logic condition input part. For the numbers range, we input the number column cell range.
We write the IF in our MEDIAN and we press Ctrl + Shift + Enter after we write the formula. As a result, we get the median for only the group B numbers!
Now, what if we have multiple criteria for our median calculation? For this, we can nest IFs in our MEDIAN or simply multiply the logic conditions for each criterion in our IF.
Here is the general writing form for both methods.
MEDIAN with nested IFs
{ = MEDIAN ( IF ( data_range1 = criterion1 , IF ( data_range2 = criterion2 … , numbers_range ) ) ) }
MEDIAN with logic conditions multiplication in IF
{ = MEDIAN ( IF ( ( data_range1 = criterion1 ) * ( data_range2 = criterion2 ) … , numbers_range ) ) }
And here is the implementation example for MEDIAN with multiple criteria. In this example, we use MEDIAN with logic conditions multiplication in its IF.
As you can see, we can get our MEDIAN with multiple criteria by using MEDIAN with logic conditions multiplication. We will get the same result if we use MEDIAN with nested IFs.
Exercise
After you have learned how to use MEDIAN in excel completely, now let’s do an exercise to practice your understanding!Download the exercise file and answer all the questions below! Download the answer key file if you have done the exercise and want to check your answers!
Link to the exercise file:
Download here
Questions
Use MEDIAN (with IF if necessary) to answer all the questions below. Give your answer in the gray-colored cell of the appropriate number in the exercise file!- What is the median of the test scores from all the students?
- There is one test score missing there, Horatio from class A with a 72 test score. What is the median of the test scores from all the students if we add Horatio’s test score too?
- What is the median of the test scores from the students in class B?
Link to the answer key file:
Download here
Additional Note
- If you have an error in your MEDIAN inputs, MEDIAN will produce an error too
- If you have a date/time in your MEDIAN inputs, MEDIAN will involve its serial number (the number which is equivalent to the date/time in excel) in the median calculation
Related tutorials you should learn from:
