Nuraida Kashmin
Sometimes we need to calculate accuracy and precision in Excel. It helps us in statistics and data analysis to avoid significant data loss and minimize the unnecessary errors that occur during calculation. In this article, we are going to learn about some quick methods to calculate accuracy and precision in Excel with some examples and explanations.
Practice Workbook
Download the following workbook and exercise.
3 Quick Methods to Calculate Accuracy in Excel
The proximity of experimental values to the true value is known as accuracy. Suppose, we have a PRODUCT dataset (B4:E9) with their forecasted demand in column C & actual demand in column D. We are going to calculate the accuracy of the forecast.
1. Apply IF Function to Calculate Accuracy in Excel
Excel IF Function is one of the logical functions which help us to check the condition as well as return one value if it’s true and another value if it’s false. Here we can use the IF function to calculate the error first and then subtract it from 1.
STEPS:
- First, select Cell E5.
- Next, type the formula:
=1-IF(C5>D5,(C5-D5)/D5,(D5-C5)/D5)
- Now hit Enter.
- Then, use the Fill Handle tool to autofill the next cells.
- After that, select the range and go to the Home tab.
- Further, select the ‘%’ from the Number section.
- Finally, we can see the accuracy percentage in Excel.
Similar Readings
- How to Round a Formula with SUM in Excel (4 Simple Ways)
- Rounding Time in Excel to Nearest Hour (6 Easy Methods)
- How to Add ROUND Formula to Multiple Cells in Excel (2 Easy Ways)
- Round Excel Data to Make Summations Correct (7 Easy Methods)
- Round Time to Nearest 5 Minutes in Excel (4 Quick Methods)
2. Calculate Accuracy with Excel ABS Function
The ABS Function in Excel returns a number’s absolute value. We can use this function to calculate the accuracy like Method 1. Let’s see how we can do this.
STEPS:
- In the beginning, select Cell E5.
- After that, write the below code:
=1-ABS(C5/D5-1)
- Then, press the Enter.
- Now, use the Fill Handle tool for the below cells.
- Further, after selecting the range, go to the Home tab.
- Next, select the ‘%’ from the Number section.
- In the end, we will see the result like the below screenshot.
3. Combine Excel MAX & MIN Functions to Get Accuracy
The MAX Function returns the maximum value whereas the MIN Function returns the minimum value of the given argument. Sometimes we can calculate the accuracy by combining the MAX & MIN functions in Excel. Let’s go through the procedure.
STEPS:
- First, select Cell E5.
- Secondly, write down the below code:
=1-(MAX(C5:D5)-MIN(C5:D5))/D5
- Thirdly, press the Enter key.
- After that, use the Fill Handle tool to apply the formula to the next cells.
- Next, select the range.
- Now, go to the Home tab.
- Then, select the ‘%’ from the Number section.
- Finally, we can see the result in the same column.
How to Set Round Precision after Calculating Accuracy in Excel
Precision refers to how closely measured quantities are related to one another. Excel helps us to round the precision and saves our time. Setting the value as the rounded value may help to avoid using functions like ROUND, ROUNDUP, and ROUNDDOWN functions. Suppose, we have the same accuracy dataset as above. Let’s go through the procedure to round precision in Excel. We may also lose the accuracy of the product as the numbers are rounded.
STEPS:
- In the beginning, go to the File tab.
- Next, select the Options feature.
- An Excel Options window will pop out.
- Now go to the Advanced group.
- Then select the ‘Set precision as displayed’ option from the ‘When calculating this workbook’ section.
- A Microsoft Excel warning message shows up here.
- Click OK to continue the process.
- Furthermore, we will decrease the decimal to set rounding precision.
- For that, select the cell range E5:E9.
- After that, go to the Home tab.
- From the Number section, select the Decrease Decimal option to round with precision.
- In the end, we will see the result below. That means the selected cells are rounded with precision.
Read More: Round to Nearest Whole Number in Excel (9 Easy Methods)
Things to Remember
There are some things that we need to remember while calculating accuracy and precision in Excel.
- If the Actual value is very much less than the Demand value, then it will return a negative or zero accuracy percentage in that case (see screenshot).
- To calculate the Error percentage instead of the accuracy percentage in the case of a huge gap between the Actual & Demand values. For that, in Cell E5 the formula:
=ABS(C5/D5-1)
- The #DIV/0! Error occurs when the Actual value becomes 0 like in the screenshot below.
- We need to put the brackets properly in the formula that we used.
- We may lose accuracy when the value is set to round with precision as it deletes extra data permanently.
- Press Alt + T + O keys to open the Excel Options feature directly.
Conclusion
By using these methods, we can quickly calculate accuracy and precision in Excel. There is a practice workbook added. Go ahead and give it a try. Visit the ExcelDemy website for more articles like this. Feel free to ask anything or suggest any new methods.
Related Articles
- How to Round Time in Excel (Discussed with 3 Examples)
- Excel VBA: Round to Nearest 5 (Macro and UDF)
- Round to Nearest 5 or 9 in Excel (8 Easy Methods)
- Rounding Time to Nearest Quarter Hour in Excel (6 Easy Methods)
- How to Round to Nearest 10 Cents in Excel (4 Suitable Methods)
- Round Off to Nearest 50 Cents in Excel (4 Quick Methods)
- How to Stop Excel from Rounding Up Decimals (4 Easy Methods)
