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.


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.

Calculate Accuracy in Excel


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)

Apply IF Function to Calculate Accuracy in Excel

  • 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


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)

Calculate Accuracy with Excel ABS Function

  • 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

Combine Excel MAX & MIN Functions to Get Accuracy

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

How to Set Round Precision after Calculating Accuracy in Excel

  • Next, select the Options feature.

How to Set Round Precision after Calculating Accuracy in Excel

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

How to Set Round Precision after Calculating Accuracy in Excel

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

Things to Remember

  • 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

Nuraida Kashmin

Nuraida Kashmin

Hi Everyone! I am Nuraida. Cordially welcome you to my profile. I am a Team Leader of Excel and VBA Content Developer in ExcelDemy. Here I will also post articles related to Microsoft Excel. With a strong educational background in Mechanical Engineering through experimental learning from Rajshahi University of Engineering & Technology. Skilled in Microsoft Word, Creative Writing, Microsoft Excel, Project Management, Support to Organize Different Events, Reporting, Monitoring & Documentation, Online Advocacy and Event Management Related to SAP and Youth Leaders.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo