How to Calculate Accuracy and Precision in Excel

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.

How to Calculate Accuracy in Excel: 3 Quick Methods

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

The 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:

• 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.

Read More: How to Calculate Forecast Accuracy Percentage in Excel

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:

• 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.

Read More: How to Forecast Revenue in Excel

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:

• 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: How to Forecast Sales Using Regression Analysis in Excel

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:

• 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.

Practice Workbook

Download the following workbook and exercise.

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. Feel free to ask anything or suggest any new methods.

Related Articles

• Time Series Forecasting Methods in Excel
• How to Forecast Call Volume in Excel
• How to Do Budgeting and Forecasting in Excel

<< Go Back to Forecasting in Excel |Excel for Finance | Learn Excel