How to Use the MEDIAN Formula in Excel: Functions, Examples, and Writing Steps - Compute Expert

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!







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 Input

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.

How to Use the MEDIAN Formula in Excel: Functions, Examples, and Writing Steps - Screenshot of the MEDIAN Implementation Example

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!

  1. Type an equal sign ( = ) in the cell where you want to put your numbers median

    How to Use the MEDIAN Formula in Excel: Functions, Examples, and Writing Steps - Screenshot of Step 1

  2. Type MEDIAN (can be with small and large letters) and an open bracket sign after =

    How to Use the MEDIAN Formula in Excel: Functions, Examples, and Writing Steps - Screenshot of Step 2

  3. 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

    How to Use the MEDIAN Formula in Excel: Functions, Examples, and Writing Steps - Screenshot of Step 3

  4. Type a close bracket sign after you have finished inputting all the numbers

    How to Use the MEDIAN Formula in Excel: Functions, Examples, and Writing Steps - Screenshot of Step 4

  5. Press Enter
  6. Done!

    How to Use the MEDIAN Formula in Excel: Functions, Examples, and Writing Steps - Screenshot of Step 6




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.

How to Use the MEDIAN Formula in Excel: Functions, Examples, and Writing Steps - Screenshot of the MEDIAN IF Implementation Example

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.

How to Use the MEDIAN Formula in Excel: Functions, Examples, and Writing Steps - Screenshot of the MEDIAN IF Implementation Example with Logic Conditions Multiplication

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!
  1. What is the median of the test scores from all the students?
  2. 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?
  3. 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:



Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!


Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!



Follow our tutorial content also on




Hand-Picked CE Tutorials

Excel Calculation

How to Sum in Excel

Subtraction in Excel

Multiplication in Excel

Division Excel Calculation

Average Excel Calculation



Excel Formula

VLOOKUP Excel Formula

Excel IF Function

SUM Formula in Excel

COUNTIF Formula in Excel

COUNT Function in Excel



Excel Tips and Trick

How to Print in Excel

Convert Number to Text Excel

Excel Worksheet Definition

Excel Range Definition

How to Add Columns in Excel



Excel Consultation

Contact Us

Privacy Policy

Affiliate Disclosure

Terms & Condition



© 2021 Compute Expert