Table of Contents
Mean Absolute Error (MAE) is a commonly used metric for measuring the accuracy of a forecasting or prediction model. It helps to assess the average difference between the predicted values and the actual values. In Excel, calculating MAE involves a few simple steps, which are outlined below.
Step 1: Gather the data
Collect the actual values and predicted values for the same set of observations. For example, you can have a column for actual values and a column for predicted values in your Excel spreadsheet.
Step 2: Calculate the absolute error
Subtract the actual values from the predicted values for each observation, ignoring the negative sign. This will give you the absolute error for each observation.
Step 3: Sum the absolute errors
Add up all the absolute errors calculated in the previous step.
Step 4: Count the number of observations
Count the total number of observations in your data set.
Step 5: Divide the sum of absolute errors by the number of observations
Divide the sum of absolute errors by the total number of observations to calculate the MAE.
Step 6: Format the result
Format the result to the desired number of decimal places to make it more readable.
In summary, MAE in Excel can be calculated by subtracting the actual values from the predicted values, summing the absolute errors, and dividing by the number of observations. It is a simple and useful measure for evaluating the accuracy of predictive models in Excel.
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) * Σ|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.