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: 3 Methods

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.

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.

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.

Download Practice Workbook

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

Accuracy Percentage in Excel.xlsx

Conclusion

Now you know 3 different ways 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.

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

Get FREE Advanced Excel Exercises with Solutions!