Calculate Absolute Percentage Error with Excel Function

The absolute percentage error function and the mean absolute percentage error function (MAPE) are the most valuable functions for calculating the accuracy of any forecasting reading or forecasting system. It is not only useful in data science and statistics but also in other scientific reports like chemistry, and even in business reports. In this article, we will use the function to calculate the absolute percentage error and mean absolute percentage error in Excel.


Download Practice Workbook

You can download the workbook used for the demonstration from the link below.


Formula for Absolute Percentage Error

As mentioned earlier, the absolute percentage error indicates the relative difference between the actual value or the observed value and the forecasted or predicted value. We can calculate the absolute error with the following formula.

So to calculate absolute percentage error, the formula would be:

absolute percentage error formula

Meanwhile, the formula for calculating the mean absolute percentage error is the following.

mean absolute percentage error formula


2 Easy Methods to Calculate Absolute Percentage Error with Excel Function

From the formulas shown above, we can see that we need to find the difference between the true value and the observed value first. In order to do that, we can subtract the smaller value from the larger one. There is also a function in Excel, the ABS function, which does the same. We have demonstrated both of these methods in two different sections below. Follow along to see how they work or find the one you need from the table above.

For both of the methods, we are going to use the following dataset.

absolute percentage error in excel dataset


1. Applying ABS Function

The ABS function in Excel returns the absolute value of the number it takes as an argument. That is the number without its sign. If we subtract any one of the actual values and forecasted values from the other, and put it in the ABS function we will get the numeric difference regardless of the position of subtraction.

The result of this function would be the absolute difference and by dividing it by the true value, we can easily get the absolute percentage error in Excel.

Follow these steps to see how we can use this for the dataset.

Steps:

  • First, let’s select cell E5.
  • Then write down the following formula in it.

=(ABS(C5-D5)/C5)

applying abs function for absolute percentage error in excel

  • After that, press Enter on your keyboard.

  • Now select the cell again and click and drag the fill handle icon to the end of the column to fill the rest of the cells with the formula.

filling out abs function in absolute percentage error in excel

  • Finally, select the range E5:E14 and go to the Home tab and click on the downward-facing arrow beside General in the Number group.
  • Then select Percentage from the drop-down list.

  • This will be the final result of our dataset.

applying abs function result for absolute percentage error in excel

This is how we can easily calculate the absolute percentage error of any reading in Excel.


2. Using IF Function

To find the difference as the positive value, we always have to subtract the lower value from the higher one. If we can identify the larger value in the first place before subtracting we can then determine the absolute error by the order of the subtraction. Given that selection process, the IF function is the perfect choice for this purpose. After that, dividing the error value by the true value will give us the absolute percentage error in Excel as usual.

Follow these steps to see how we can utilize this function for the purpose.

Steps:

  • First, select cell E5.
  • Now write down the following formula in the cell.

=IF(D5>C5,(D5-C5)/C5,(C5-D5)/C5)

using if function for absolute percentage error in excel

  • Then press Enter.

  • Finally, select the cell again and click and drag the fill handle icon to the end of the column to fill the rest of the cells with the formula.

filling if formula for absolute percentage error in excel

  • Now select the range E5:E14 and go to the Home tab and click on the downward-facing arrow beside General in the Number group.
  • Then select Percentage from the drop-down list.

  • Finally, the dataset will look like this.

result of if function in absolute percentage error in excel

This is another way to calculate the absolute percentage error in Excel.


How to Calculate Mean Absolute Percentage Error in Excel (MAPE Calculation)

There is another important term in forecasting called the mean absolute percentage error or MAPE. This value indicates the finesse of the whole forecasting. In terms of numbers, this is just the mean of the absolute percentage error we calculated above. But this indicates the average deviation of each forecasted value from the observation. So we can measure the average deviation from this term.

Regardless of how we calculate the absolute percentage error, we can easily calculate the mean absolute percentage error (MAPE) from it in Excel by using the AVERAGE function. This function takes several values and returns the mean of them. We will go for using the ABS function to calculate the absolute error.

Follow these steps to see how we can determine the mean absolute percentage error (MAPE) in Excel.

Steps:

  • First, select cell E5.
  • Then write down the following formula.

=(ABS(C5-D5)/C5)

applying abs function for mean absolute percentage error in excel

  • After that, press Enter.

mean absolute percentage error of the first reading in excel

  • Then fill out the rest of the cells with the formula by selecting cell E5 again and clicking and dragging it all the way down.

  • Now select the range E5:E14 and go to the Home tab and click on the downward-facing arrow beside General in the Number group.
  • Then select Percentage from the drop-down list.

  • Next, select a cell for the mean absolute percentage value (we are choosing cell E16 for this) and write down the following formula.

=AVERAGE(E5:E14)

applying average function for mean absolute percentage error

  • Finally, press Enter and modify it to the percentage format.

calculating mean absolute percentage error in Excel

This is how we can calculate the mean absolute percentage error (MAPE) in Excel.


Conclusion

That concludes this article for the absolute percentage error function and mean absolute percentage error in Excel. Hopefully, you have understood the application of the formula using the Excel functions and can apply such in your own scenario. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know in the comments below.

For more guides like this, visit Exceldemy.com.

Abrar Niloy

Abrar Niloy

Hi! my name is Abrar-ur-Rahman Niloy. I have completed B.Sc. in Naval Architecture and Marine Engineering. I have found my calling, if you like, in Data Science and Machine Learning and in pursuing so, I have realized the importance of Data Analysis. And Excel is one excel-lent tool do so. I am always trying to learn everyday, and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo