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.
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.
- 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.
- 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
- Firstly, enter the following formula in cell E5.
- 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.
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.