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:
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:
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?
The answer is No!
There were two ad-hoc events in the week analysed, the flash sale was a one-off and will not be repeated again and all things being well the website will not be down again so sales should be made every day of the week.
In this case the Median would be a better way to estimate the daily sales for next week and the Median is shown as 10 sales per day and being two sales less per day than the average it is a significant difference for this example, imagine if you were the sales person being targeted!
Again this is a basic example designed to help you understand the concept of the Median formula in Excel and how it can differ to the Average formula but you can see how using the wrong measure can have quite a significant impact on certain datasets. Always consider your dataset and whether outliers exist or could be an important factor in the result.
Hopefully you can now calculate the Median in Excel and also explain to your manager or client why you have chosen the Median over an Average. If in doubt calculate both for your client and explain the difference.