Mean Absolute Error (MAE) in Excel

How To Calculate Mean Absolute Error (MAE) In Excel

by

Mean Absolute Error is one of the easiest ways to find out the accuracy of any data set. It also measures the error between the expected and observed values. In this post, we are going to see how to calculate Mean Absolute Error (MAE) In Excel.

Calculate Mean Absolute Error (MAE) in Excel

It is not similar to the Root Mean Square Error (RMSE). So without further delay, let’s jump into the topic. 

Mean Absolute Error (MAE) = (1/n) * Σ|yi – xi|

where:

Σ: means sum
yi: observed value
xi: predicted value
n: total number of observations

1. Enter the data value

The first step is to insert observed and expected values in separate columns.

Enter the data value

2. Find The Absolute Differences

Now we are going to use the formula =(B2-C2) to calculate the Absolute Differences. The final value can be negative so don’t be worried about that.

Find The Absolute Differences

3. Find Out MAE

In the next step, we are going to use this formula =SUMPRODUCT(E2:E7)/COUNT(E2:E7) to calculate the mean absolute error. Before that, we need to get rid of these ‘-‘ signs by using the formula =ABS(D2).

mean absolute error

In my case, MAE turns out to be 4.16666. This value indicates the average absolute difference between the observed value and the expected value. The lower the value of MAE the better it fits the data set.

So, guys, this is all about Mean Absolute Error (MAE) in Excel. If you have any issues regarding this topic then comment down below.

Leave a Comment