How to Calculate Median Absolute Deviation in Excel

This guide will explain how to calculate the median absolute deviation in Excel using the MEDIAN function. 

The rules for using the MEDIAN function in Excel are the following:

• The MEDIAN function performs varied calculations depending on the number of numeric data values provided or selected.  
• When the number of data values is odd, the function will return the middle number in the data set.
• When the number of data values is even, the function will return the average of the two numbers found in the middle of the data set.
• We can input a hardcoded constant, a cell reference, or a range, in any combination as arguments. However, the function will ignore cells that are empty or blank, text values, and logical values such as TRUE or FALSE.
• The MEDIAN function can only support up to 255 separate arguments. 
• If there are no numeric values found in the provided arguments, the function will return a #NUM! error.

Excel is an excellent tool to use for performing different statistical calculations. For instance, we can easily and quickly calculate the median absolute deviation in Excel with the help of its built-in functions and tools.

Furthermore, the median absolute deviation or also known as MAD, is used to measure the spread of values in a data set. So the median absolute deviation is similar to the mean absolute deviation in which both measure the spread or dispersion of a data set.

However, the median absolute deviation determines the spread or variability of the values in a data set based on the median. So the median absolute deviation is essentially the average distance of the data values from the median of the data set.

Let’s take a sample scenario wherein we need to calculate the median absolute deviation in Excel.

Suppose you have a data set containing 10 data values. And you want to obtain a numerical description of how dispersed or spread out the data values of the data set are. To do this, you decided to calculate the median absolute deviation of your data set using the MEDIAN function. 

Before we continue on to a real example of calculating the median absolute deviation in Excel, let’s first learn how to write and use the MEDIAN function in Excel.

The Anatomy of the MEDIAN Function

The syntax or the way we write the MEDIAN function is as follows:

=MEDIAN(number1, [number2],...)

Let’s take apart this formula and understand what each term means:

• = the equal sign is how we start any function in Excel.
• MEDIAN() is our MEDIAN function. And this function is used to return the median or the number in the middle of the given or selected data set.
• number1 is a required argument. So this refers to 1 to 255 numbers or names, arrays, or cell references with numbers for which we want to determine the median.
• number2 is an optional argument. And this acts as a supplement to the argument we already inputted in the number1 argument. So this is still referring to 1 to 255 numbers or names, arrays, or cell references containing numbers for which we also want to find the median.

Great! Now we can move on and dive into a real example of calculating the median absolute deviation in Excel.

A Real Example of Calculating Median Absolute Deviation in Excel

Let’s say we have a data set containing 10 data values. Since we want to determine the data set’s variability, we want to calculate the median absolute deviation of the data values. So our initial data set would look like this:

Moreover, the median absolute deviation, which is often abbreviated as MAD, is a simple and easy method to measure the spread of values in our data set. Essentially, the median absolute deviation is simply the average distance of the data values from the median. 

Furthermore, the purpose of the median absolute deviation is to give us an idea of how dispersed or spread out the data values within the data set are. Since the median absolute deviation is based on the distance from the median, we will utilize the MEDIAN function in Excel to help us calculate. 

Additionally, the formula for the median absolute deviation is = median(|xi – xm|), wherein xi refers to the ith data value in our data set and xm refers to the median value in our data set.

Firstly, we will find the median of our data set using the MEDIAN function. So the MEDIAN function is used to return the median or the number in the middle of our data set.

Secondly, we will calculate the absolute difference between each value and the median. In this case, we will utilize the ABS function. So the ABS function returns the absolute value or a value without its sign. And we will do this for each data value in our data set.

Lastly, we can calculate the median absolute deviation using the MEDIAN function again. So we will simply find the median of the absolute differences in our data set. And our final data set would look like this:

You can make your own copy of the spreadsheet above using the link attached below. 

Amazing! Now we can proceed and explain the steps of how to calculate the median absolute deviation in Excel using the MEDIAN function.

How to Calculate Median Absolute Deviation in Excel

In this section, we will discuss the step-by-step process of how to calculate the median absolute deviation in Excel. Additionally, each step contains detailed instructions and pictures to guide you through the process.

To apply this method, simply follow the steps below.

1. Firstly, we need to find the median of the data set using the MEDIAN function. To do this, we will type in the formula “=MEDIAN(B2:B11)”. Then, we will press the Enter key to return the result.

2. Secondly, we will calculate the absolute difference of each data value in our data set with the help of the ABS function. So we will input the formula “=ABS(B2-$F$2)”. Lastly, we will press the Enter key to return the result.

3. Thirdly, we will drag down the Fill Handle tool to copy and apply the formula to the other cells.

4. Next, we can finally calculate the median absolute deviation. To do this, we will type in the formula “=MEDIAN(C2:C11)”. Lastly, we will press the Enter key to return the result.

5. And tada! We have successfully calculated the median absolute deviation in Excel.

And that’s pretty much it! We have explained the step-by-step process of how to calculate the median absolute deviation in Excel. Now you can apply this easy and simple method to your work whenever you need to determine the median absolute deviation of your data set.

Are you interested in learning more about what Excel can do? You can now use the MEDIAN function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

Subscribe