How to Calculate Accuracy Percentage in Excel (3 Methods)

This article illustrates how to calculate accuracy percentage in excel. You may need to calculate the accuracy percentage of the demand forecasting for your business to restructure the forecasting model or improve its accuracy. This may also become necessary while determining whether buying a product at a particular discount rate is a good deal. You can easily do the necessary calculations with some simple formulas in excel in those circumstances. Have a quick look through the article to learn how to get the desired results highlighted in the following picture.

How to Calculate Accuracy Percentage in Excel


Download Practice Workbook

You can download the practice workbook from the download button below.


3 Methods to Calculate Accuracy Percentage in Excel

Assume you have the following dataset. It contains the forecasted demand units and actual demand units for some products. Now you want to calculate the accuracy percentage of the forecast. Then follow the methods below.


1. Calculate Accuracy Percentage with Excel ABS Function

You can use the ABS function in excel to calculate the percentage of the error first. Then, subtracting the error from 1 will give you the accuracy percentage.

  • First, enter the following formula in cell E5 as shown in the following picture.
=1-ABS(C5/D5-1)
  • Then, click on the % icon to apply the percentage number format.
  • After that, double-click on the Fill Handle icon or drag it down to apply the formula to the cells below.

Calculate Accuracy Percentage with Excel ABS Function


2. Compute Accuracy Percentage with the IF Function

You can replace the ABS function with the IF function in excel to get the same result.

  • First, apply the following formula in cell E5 as shown below.
=1-IF(C5>D5,(C5-D5)/D5,(D5-C5)/D5)
  • Next, apply the percentage number format in that cell by clicking on the % icon from the Home tab.
  • After that, apply the formula to all the cells below using the Fill Handle icon.

Calculate Accuracy Percentage with Excel IF Function


3. Use MIN-MAX Combination to Calculate Accuracy Percentage in Excel

Alternatively, you can use the MAX and MIN functions in excel for that. The MAX function returns the maximum value within a range whereas the MIN function returns the minimum value.

  • Firstly, enter the following formula in cell E5.
=1-(MAX(C5:D5)-MIN(C5:D5))/D5
  • Secondly, select the percentage number format using the % icon as shown in the picture below.
  • Finally, populate all the cells below with the formula by using the Fill Handle icon.


Errors That Occur in Accuracy Percentage Calculation in Excel

You may run into some errors while calculating the accuracy percentage in excel. One of the errors occurs when the Forecast data is much larger than the Demand value. This results in a negative or zero accuracy percentage as shown in the following picture.

  • In that case, it is more suitable to calculate the error percentage than the accuracy percentage.

  • On the other hand, if the Demand value becomes zero, then excel will show the #DIV/0! Error as shown in the picture below.


Things to Remember

  • You must use the actual value (Demand) in the denominator in the formula while calculating the accuracy percentage.
  • Make sure you have used the brackets properly in the formulas.

Conclusion

Now you know 3 different ways how to calculate accuracy percentage in excel. Please let us know if this article has helped you with your problem. You can also use the comment section below for further queries or suggestions. Do visit our ExcelDemy blog to explore more on excel. Stay with us and keep learning.

Md. Shamim Reza

Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo