How to Calculate Error Percentage in Excel (3 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

The theoretical data and experimental data don’t always match. In that case, we can calculate the error percentage by subtracting theoretical data from experimental data. The error can be calculated as a percentage of theoretical data. In this article, we will show you 3 easy methods to calculate the error percentage in Excel.


How to Calculate Error Percentage in Excel: 3 Easy Methods

We can calculate error by subtracting theoretical data from theoretical data. If we divide the error by the theoretical data and multiply it by 100 we get the error percentage. Here we will discuss 3 easy and simple methods to calculate the error percentage in Excel.


Method 1: Calculate Error Percentage Using Percent Error Formula in Excel

We can apply a general formula to get the error percentage in Excel. We are showing below the steps for doing that.

  • First, we create a dataset. It includes some experimental and theoretical data from which we will calculate the error percentage.

how to calculate error percentage in excel

  • Then we have to write the following formula in cell D5 and press Enter.
=(B5-C5)*100/C5
  • Use the Fill Handle to copy the formula in the cells below.

Here, B5 – C5 denotes the error, we divide it by C5 which contains the theoretical value, and multiply it by 100 to get the percentage error.
  • We can see the percentage error for the set of data.

Method 2: Apply Excel Percentage Format for Error Percentage Calculation

We can also calculate the decimal value of the error first and apply the Percentage format to the decimal value to get the error percentage. We are showing the steps below.

  • First, we write the following formula in the cell D5.
=(B5-C5)/C5
  • Next, hit Enter.
  • Then use Fill Handle to copy the formula in the cells below.

Apply Excel Percentage Format for Error Percentage Calculation

Here, B5 – C5 gives the error and by dividing it with C5 (theoretical data), we get the comparative error in decimal.
  • We select cells (E5:E7) where we want to get the error percentage.

  • Afterwards we select the percentage format from the Home tab in the ribbon.

  • Later on we write the following formula in the cell E5 and press Enter.
=D5
  • Now, use Fill Handle to copy the formula in the cells below.

Here, D5 contains the comparative error in decimal.
  • Hurrah! We can see the error percentage.

Read More: How to Calculate Mean Percentage Error in Excel


Method 3: Use ABS Function to Calculate Mean Absolute Percentage Error

So far we calculated the error percentage which can be both positive or negative but we may need to get the absolute value of the error. Further, we may want the mean of absolute error percentage for a set of data. We will show the steps to calculate the mean absolute percentage error in Excel here.

  • First, we calculate the comparative error in decimal by writing the following formula in cell D5.
=(B5-C5)/C5
  • Next, hit Enter.
  • After that, use the Fill Handle tool to the next cells to see the results or errors.

Use ABS Function to Calculate Mean Absolute Percentage Error

Here, B5 – C5 gives the error, we divide it by C5 (theoretical data) to get the comparative error in decimal.
  • Then we write the following formula in the cell E5.
=ABS(D5)
  • Further, press Enter from the keyboard.
  • Again, use Fill Handle to copy the formula in the cells below.

Here, we used The ABS Function of Excel to get the absolute value of cell D5.
  • Now, we write the following formula in the cell E9.
=SUM(E5:E7)/COUNT(E5:E7)
  • Hit Enter to get the absolute mean percentage error.

We used The SUM Function to add the absolute error percentage for data in the range E5:E7. The COUNT Function counts the number of data in the range E5:E7. We used the division operator (/) to get the mean value.
  • Yahoo! We have successfully calculated the absolute mean percentage error.

Read More: Make an Excel Spreadsheet Automatically Calculate Percentage


Download Practice Workbook

You can download the practice workbook from here.


Conclusion

Error percentage is very helpful to assess the accuracy of an experiment. In this article, we have shown 3 different methods to calculate the error percentage in Excel. If you have any queries or suggestions, please let us know by commenting.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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