Method 1 – Calculate the Error Percentage Using the Percent Error Formula in Excel
- Create a dataset.
- Enter the following formula in D5 and press Enter.
=(B5-C5)*100/C5- Drag down the Fill Handle to see the result in the rest of the cells.
B5 – C5 is the error. It is divided by C5 which contains the theoretical value, and multiplied by 100 to get the percentage error.
Method 2 – Applying the Excel Percentage Format
- Enter the following formula in D5 and press Enter.
=(B5-C5)/C5- Drag down the Fill Handle to see the result in the rest of the cells.
B5 – C5 is the error. It is divide by C5 (theoretical data) to return the comparative error in decimals.
- Select E5:E7 to see the error percentage.
- Select the percentage format in the Home tab.
- Enter the following formula in E5 and press Enter.
=D5- Drag down the Fill Handle to see the result in the rest of the cells.
D5 contains the comparative error in decimals.
Method 3 – Use the ABS Function to Calculate the Mean Absolute Percentage Error
- Calculate the comparative error in decimals by using the following formula in D5.
=(B5-C5)/C5- Press Enter.
- Drag down the Fill Handle to see the result in the rest of the cells.
B5 – C5 is the error. It is divided by C5 (theoretical data) to get the comparative error in decimals.
- Use the following formula in the E5.
=ABS(D5)- Press Enter.
- Drag down the Fill Handle to see the result in the rest of the cells.
The ABS Function returns the absolute value of D5.
- Enter the following formula in E9.
=SUM(E5:E7)/COUNT(E5:E7)- Press Enter to see the absolute mean percentage error.
The SUM Function adds the absolute error percentage in E5:E7. The COUNT Function counts data in E5:E7. The division operator (/) returns the mean value.
Read More: Make an Excel Spreadsheet Automatically Calculate Percentage
Download Practice Workbook
Download the practice workbook.
Related Articles
- How to Calculate Total Percentage from Multiple Percentages in Excel
- How to Calculate Percentage of Percentage in Excel
- How to Calculate Percentage Based on Conditional Formatting
- How to Calculate Percentage in Excel Based on Cell Color
- Percentage Showing as Thousand in Excel
- Why Are My Percentages Wrong in Excel?
- How to Remove Percentage in Excel
- How to Find the Percentage of Two Numbers in Excel
- How to Calculate Percentage of Month in Excel
- How to Calculate Percentage Complete Based on Dates in Excel
- How to Calculate Cumulative Percentage in Excel
<< Go Back to Calculating Percentages | Calculate in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
