How to Calculate the Error Percentage in Excel – 3 Methods

 

 

Method 1 – Calculate the Error Percentage Using the Percent Error Formula in Excel

  • Create a dataset.

how to calculate error percentage in excel

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

Apply Excel Percentage Format for Error Percentage Calculation

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.

 

Read More: How to Calculate Mean Percentage Error in Excel


Method 3 – Use the ABS Function to Calculate the Mean Absolute Percentage Error

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.

Use ABS Function to Calculate Mean Absolute Percentage Error

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


<< Go Back to Percentage Formula ExamplesCalculating Percentages | Calculate in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Mehedi Hasan Shimul
Mehedi Hasan Shimul

Md. Mehedi Hasan, with a BSc in Electrical & Electronic Engineering from Bangladesh University of Engineering and Technology, holds a crucial position as an Excel & VBA Content Developer at ExcelDemy. Driven by a deep passion for research and innovation, he actively immerses himself in Excel. In his role, Mehedi not only skillfully addresses complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his steadfast commitment to consistently deliver exceptional and quality content.... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo