Table of Contents
Mean Absolute Error (MAE) in Excel is calculated by subtracting the actual value from the predicted value for each data point, and then taking the average of the absolute value of those differences. This formula can be expressed as ∑|actual-predicted|/n, where n is the number of data points. Using the SUM and ABS functions in Excel, you can quickly calculate MAE for a dataset.
In statistics, the mean absolute error (MAE) is a way to measure the accuracy of a given model. It is calculated as:
MAE = (1/n) * Σ|yi – xi|
where:
- Σ: A Greek symbol that means “sum”
- yi: The observed value for the ith observation
- xi: The predicted value for the ith observation
- n: The total number of observations
The following step-by-step example shows how to calculate the mean absolute error in Excel.
Step 1: Enter the Data
First, let’s enter a list of observed and predicted values in two separate columns:
Note: Use to if you need to learn how to use a regression model to calculate predicted values.
Step 2: Calculate the Absolute Differences
Next, we’ll use the following formula to calculate the absolute differences between the observed and predicted values:
Step 3: Calculate MAE
Next, we’ll use the following formula to calculate the mean absolute error:
The mean absolute error (MAE) turns out to be 2.5625.
This tells us that the average absolute difference between the observed values and the predicted values is 2.5625.
Bonus: Feel free to use this to automatically calculate the MAE for a list of observed and predicted values.