Formula to Calculate the Absolute Percentage Error
The absolute percentage error indicates the relative difference between the actual value and the forecast value. The formula is:
To calculate the absolute percentage error, the formula is:
The formula to calculate the mean absolute percentage error is:
This is the sample dataset.
Method 1 – Applying the ABS Function
Steps:
- Select E5.
- Enter the following formula:
=(ABS(C5-D5)/C5)
- Press Enter.
- Select the cell again.
- Drag down the Fill Handle to see the result in the rest of the cells.
- Select E5:E14 and go to the Home tab.
- Click the downward arrow beside General in Number.
- Select Percentage.
This is the output.
Read More: Opposite of ABS Function in Excel
Method 2 – Using the IF Function
Steps:
- Select E5.
- Enter the following formula:
=IF(D5>C5,(D5-C5)/C5,(C5-D5)/C5)
- Press Enter.
- Select the cell again.
- Drag down the Fill Handle to see the result in the rest of the cells.
- Select E5:E14 and go to the Home tab.
- Click the downward arrow beside General in Number.
- Select Percentage.
This is the output.
Read More: Changing Negative Numbers to Positive in Excel
How to Calculate the Mean Absolute Percentage Error in Excel (MAPE Calculation)
Steps:
- Select E5.
- Enter the following formula:
=(ABS(C5-D5)/C5)
- Press Enter.
- Drag down the Fill Handle to see the result in the rest of the cells.
- Select E5:E14 and go to the Home tab.
- Click the downward arrow beside General in Number.
- Select Percentage.
- Select a cell to see the mean absolute percentage (here, E16) and enter the following formula.
=AVERAGE(E5:E14)
- Press Enter to see the result.
Read More: How to Sum Absolute Value in Excel
Download Practice Workbook
Download the workbook.
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
Get FREE Advanced Excel Exercises with Solutions!