How to Calculate Mean Absolute Error in Excel (Step-by-Step)

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) * Σ|y_i – x_i|

where:

• Σ: A Greek symbol that means “sum”
• y_i: The observed value for the i^th observation
• x_i: The predicted value for the i^th 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.

Additional Resources

How to Calculate MAPE in Excel
How to Calculate SMAPE in Excel