MAD (Mean Absolute Deviation) is a measure of the average absolute deviation of each data point from the mean of a data set. It provides a summary of the variability in the data.

Steps to Calculate MAD in Excel

  1. Calculate the Mean:
  • Use the AVERAGE function to find the mean of your data set.
  1. Calculate the Absolute Deviations:
  • Subtract the mean from each data point and take the absolute value. Use the ABS function for this.
  1. Calculate the Mean of the Absolute Deviations:
  • Use the AVERAGE function again to find the mean of the absolute deviations.

Live Example

Suppose we have the following data set in Excel in cells A1 to A5:

| 10 |
| 12 |
| 14 |
| 16 |
| 18 |

Step-by-Step Calculation in Excel

  1. Calculate the Mean:
  • In cell B1, enter =AVERAGE(A1:A5). The result will be 14.
  1. Calculate the Absolute Deviations:
  • In cell B2, enter =ABS(A1-$B$1), and drag the fill handle down to cell B6. This will calculate the absolute deviations from the mean for each data point. Results:
   | 4 |
   | 2 |
   | 0 |
   | 2 |
   | 4 |
  1. Calculate the Mean of the Absolute Deviations:
  • In cell B7, enter =AVERAGE(B2:B6). The result will be 2.4.

The MAD of the data set is 2.4.

Excel Screenshot

Below is a representation of what the Excel sheet might look like:

| A   | B                    |
|---- |----------------------|
| 10  | =ABS(A1-$B$1)        |
| 12  | =ABS(A2-$B$1)        |
| 14  | =ABS(A3-$B$1)        |
| 16  | =ABS(A4-$B$1)        |
| 18  | =ABS(A5-$B$1)        |
| 14  | =AVERAGE(A1:A5)      |
| 2.4 | =AVERAGE(B1:B5)      |

Summary

  1. Enter your data in column A.
  2. Calculate the mean using =AVERAGE(A1:A5).
  3. Calculate absolute deviations using =ABS(A1-$B$1) for each data point.
  4. Calculate MAD using =AVERAGE(B1:B5).

MAD is a valuable statistical measure for understanding the dispersion or variability in a data set, providing insights into the consistency and reliability of the data.

By following the steps outlined—inputting your data, calculating the mean, finding the absolute deviations, and then averaging these deviations—you can efficiently determine the MAD value in Excel. This measure helps to quantify the average distance between each data point and the mean, making it a useful tool for data analysis in various fields, including finance, quality control, and research.


0 Comments

Leave a Reply

Avatar placeholder

Your email address will not be published. Required fields are marked *