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|
- Σ: 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 this tutorial 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.
In general, the lower the value for the MAE the better a model is able to fit a dataset. When comparing two different models, we can compare the MAE of each model to know which one offers a better fit to a dataset.
Bonus: Feel free to use this Mean Absolute Error Calculator to automatically calculate the MAE for a list of observed and predicted values.
How to Calculate MAPE in Excel
How to Calculate SMAPE in Excel