# Median Formula in Excel

To calculate the Median in Excel all you need to do is use the Median Formula.  Using the Median formula in Excel is a basic concept that any Excel Analyst can use in their calculations and understand with ease, this post will show you how to calculate the Median in Excel and also help you understand when to use it and why…

Median Formula in Excel

The Median formula, or Median function, is a statistical calculation in Excel and it takes the format:

=MEDIAN (number1, number2, number3…)

This is where “number1, 2, 3” etc. represent the range of cells that you want in your Median calculation.

In the below example there are four business areas (A, B, C and D) along with their sales, calculating the Median in Excel is achieved by using the formula:

=MEDIAN (B4:B7)

The Median is the mid-point of all the values in our range when they are sorted in order (ascending or descending).  In the example shown above there are four values so ordered from smallest to largest they would look like:

The mid-point is the value between the second (200) and third (250) values.  The middle of 200 and 250 is 225, you could calculate that by:

= ((200+250)/2)

Another example of the Median formula in Excel

If we change the previous example and have seven values instead of four then which one would be the Median?

The mid-point of seven values is the fourth value:

Why use the Median formula in Excel?

Generally an Excel analyst would use the Median on much larger datasets than shown in the examples.  The examples are simple in order to give you a clear understanding of not only how to apply the Median formula in Excel but also to understand how the Median is calculated.

A Median is useful as because it is looking for the mid-point in a range of values it is ignoring any outliers (high and low ones) that could otherwise skew the information if you just used the Average formula in Excel.

To highlight the difference between using the Average formula and the Median formula in Excel take a basic example of a business that is analysing its sales volumes over the past 7 days.

In this example the business had a one-off flash sale on Day 4 which led to increased sales volumes and on Day 7 the website had to be shut-down for some maintenance which unfortunately for the business led to zero sales being made that day:

As you can see above if you use the Average formula it would show an average of 12 sales per day throughout the week, this is perfectly true and an acceptable way of displaying the average for that week but would it be the best way of estimating daily sales for next week?