Excel MEDIAN Function

In Mathematics, the median number represents the number lying at the midpoint of a frequency distribution of observed values such that there is an equal probability of the remaining values in the distribution falling above or below it. The main rule when calculating the median is to sort all numerical values in ascending or descending order (usually the former), and this also applies when using the MEDIAN function. Thankfully however, excel does the sorting automatically when we use the MEDIAN Function.

In today’s post, we would touch on all you need to know about the MEDIAN Function, and we would provide an example on how you can incorporate this function in financial modeling. Before we begin however, let’s take a look at the Syntax & Argument for the MEDIAN Function

Syntax & Argument

The Syntax for the MEDIAN Function is as follows:

=MEDIAN(number1, [number2, …])

The MEDIAN function takes multiple arguments in the form of number1, number2, number3 and so on, however only 1 argument (number1) is needed to make it work. Let’s see more details on the first 2 arguments below:

number1: Hardcoded number or cell reference (or range of cells) that refers to numerical values. If you select a range of cells as your number1 argument, excel automatically sorts the values and returns the median value.

number2: This is also a hardcoded number or cell reference that refers to numerical values. It is optional, as the first argument (number1) is sufficient regardless of whether it refers to a single number or a range of cells containing numerical values.

Usage Notes

The following are things to note when using the MEDIAN Function

• The MEDIAN Function can process both hardcoded numbers and cells or range of cells containing numerical values.
• The MEDIAN Funtion could take up to 255 separate arguments
• It ignores cells without numerical values, meaning that empty cells and cells containing text values and logical values TRUE and FALSE are ignored.

Now let’s see some examples of the MEDIAN Function below:

MEDIAN Function Examples

The MEDIAN Function can be used with only hardcoded numbers inputted within the formula

We can also have a scenario where all the numbers above are inputted in cell. We only need to select these cells as our argument to return the appropriate median value.

In the previous example we treated, the number of values provided was Odd. How about when we have an even number of values? Excel treats the set the same way it would be treated in Mathematics, by taking the average of the two middle numbers.

Integrating the MEDIAN Function in Financial Modeling

Let’s imagine that a real estate company is looking to invest in a residential property consisting of multiple units of 1-bedroom apartments in the US. For valuation and approval of Investment, the Investment associate is to use a sample 1 bedroom apartment rental cost in 29 cities across USA. Ideally, an average would suffice, but as we would see shortly below, some of these cities in our sample data have rental values that are outliers. Using an average with these values included will skew the results and give us an unrealistic forecast. To be safe and conservative, it is advisable to use the median rent value for the valuation.

Why not use average? Well, as we said in the opening paragraph of this section, there may be some outliers in the data set which would skew the average rent value thereby leading to an over valued property meaning that the investment company would pay more than the value’s true worth. Let’s see some of the outliers in our sample data:

As you guessed, the outliers have an arrow in front of them. We would be using the Median function for a more conservative valuation. Let’s compare the Median and average values below. Remember, to use the Median function, just select the range of cells and excel would do the sorting.

The difference between our median rent and average rent is $117, largely due to the outliers highlighted above.

Download Excel File