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.

## 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:

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

## Calculate Absolute Percentage Error with Excel Function: 2 Easy Methods

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.

### 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)`

- 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.

- 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.

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

**Read More: Opposite of ABS Function 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)`

- 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.

- 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.

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

**Read More: Changing Negative Numbers to Positive 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)`

- After that, press
**Enter**.

- 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)`

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

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

**Read More: How to Sum Absolute Value in Excel**

**Download Practice Workbook**

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

## 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.

## Related Articles

**How to Make All Numbers Positive in Excel****How to Calculate Absolute Difference between Two Numbers in Excel**

**<< Go Back toÂ Excel ABS Function | Excel Functions | Learn Excel**