MEDIAN Function

What Is The MEDIAN Function?

The MEDIAN function is an Excel Statistical function that calculates the middlemost value in a data set. It is used to identify the median, which tends to separate the lower half from the higher half.

The function provides the result as the middlemost number when the numbers are odd. When the numbers are even, the average of the middle two numbers is taken as the median.

Before getting started on how to use and when to use the median function in Excel, it should be known how to find the median in math without Excel. The median is typically the middle number that you have in a sequence of numbers or data sets.

In math, the usual way to do it is first to put all your numbers in order. After putting them in order from least to greatest, you pick the one in the middle after crossing each number out on each side evenly.

Typically, you would have to have an odd set of numbers to get the middle number. This is because our goal is to get the last standing number from the data.

If we have an even set of even numbers, we will end up with two middle numbers. However, having two middle numbers does not mean we won’t be able to find the median. We would just have to take extra steps to find it.

For example, if you have five numbers: 4,6,2,8,9, we would put them in order.

After arranging the numbers in order, your first step is complete. The next step that should be taken would be crossing the numbers out until you reach your middle number.

The middle value is six, which is your median. The median separates the higher half value from the lower half value.

If there is an instance where you have two numbers that are in the middle, then you would proceed to find the mean of the two numbers. You would add the two middle-value numbers and divide the sum by two.

The numbers you pick also do not have to be single-digit numbers; they can work with any range of numbers.

We often learned about the median in school. When we learned about the median, we were also taught about the mean and mode, which are also functions in Excel.

It is always good to remember what these are used for because you might use them. For example, in the median, you must use the mean to find the median when there is a set of even numbers.

Understanding the MEDIAN function

When working on Excel, you are usually using large sets of data. Finding the median, as we did above, would take a very long time. Therefore, Excel provides a median function to make it easier for us.

Using the median function in Excel will still give us the middle-value number from your data set. Typically when you work in finance or other jobs, such as actuaries that use large sets of data, you would use Excel to help. The function can give you the point at which your data is centered.

Excel is not only for people with jobs; it can also be used by students who just need a system to help them. The function would help identify their middle number from a large data set much easier than if they were to do it by hand. 

MEDIAN Function Formula

The first thing one should know is the formula for it in Excel.

=MEDIAN(number 1, [number2],…)

This is an example of how it looks when entering the formula. The function on Microsoft Excel has the following arguments:

• Number 1 input is required. The following numbers are optional.
• Number 2 and any other number following are optional. 

Take note on

1. You can find the median on either one number or up to two-hundred and fifty-five numbers.
2. Any argument can range from numbers, arrays, references, or names that have to contain numbers. 
3. Any rational value or a word representation of a number typed into the formula will be counted. 
4. Any argument that is an error value or if there is text that the system can not translate into a number will cause errors. 
5. If there are empty cells, those values are ignored. However, if there is a cell with the number zero, then the system will include it. 
6. If you contain Excel 2007 and up, the function will accept up to two hundred fifty-five arguments. However, if you have older versions, such as Excel 2003 and under, you may only be able to enter about 30 arguments. 

Examples Of MEDIAN Function

Let’s proceed with finding a few examples of median to help learn how to use the function.

In the following example, we will start by just trying to find the middle value using an odd set of values. 

Example #1

Notice that you can click on the cells you want to be added to your formula, and the formula will adjust itself with the cells’ names.

After the formula is completed or when you press the enter key on your keyboard, the system will solve it for you. In this case, our median was three. The system considered our repetitive numbers and the zero.

Example #2

Now, let's follow with an even set of numbers.

The result was 2.5 because since the set of numbers is even, the system knows that it needs to take extra steps to find the middle set value.

Example #3

For this example, we will enter the logical values such as TRUE and FALSE. Even though we have the logical value in the cells, we will still get the middle number.

Logical values like true and false are ignored, but you will still get the middle value.

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• MODE.MULT Function
• MODE.SNGL Function
• PEARSON Function
• PERCENTILE Function
• QUARTILE Function