How to Calculate Accuracy and Precision in Excel

The proximity of experimental values to the true value is known as accuracy. Suppose we have a PRODUCT dataset (B4:E9) with forecasted demand in column C and actual demand in column D. We are going to calculate the accuracy of the forecast.

Calculate Accuracy in Excel


Method 1 – Apply the IF Function to Calculate Accuracy in Excel

Steps:

  • Select cell E5.
  • Insert the following formula:
=1-IF(C5>D5,(C5-D5)/D5,(D5-C5)/D5)

Apply IF Function to Calculate Accuracy in Excel

  • Hit Enter.
  • Use the Fill Handle tool to autofill the rest of the column.
  • Select the range and go to the Home tab.
  • Select ‘%’ from the Number section.

  • We can see the accuracy percentages in Excel.

Read More: How to Calculate Forecast Accuracy Percentage in Excel


Method 2 – Calculate Accuracy with the Excel ABS Function

Steps:

  • Select Cell E5.
  • Insert the following code:
=1-ABS(C5/D5-1)

Calculate Accuracy with Excel ABS Function

  • Press Enter.
  • Use the Fill Handle tool for the other cells.
  • Select the results and go to the Home tab.
  • Select the ‘%’ icon from the Number section.

  • The accuracy is now displayed in percentages.

Read More: How to Forecast Revenue in Excel


Method 3 – Combine Excel MAX and MIN Functions to Get Accuracy

Steps:

  • Select Cell E5.
  • Insert the following code:
=1-(MAX(C5:D5)-MIN(C5:D5))/D5

Combine Excel MAX & MIN Functions to Get Accuracy

  • Press the Enter key.
  • Use the Fill Handle tool to apply the formula to the other cells.
  • Select the result range.
  • Go to the Home tab.
  • Select the ‘%’ icon from the Number section.

  • We can see the result in the column.


How to Round the Precision After Calculating Accuracy in Excel

Steps:

  • Go to the File tab.

How to Set Round Precision after Calculating Accuracy in Excel

  • Select Options.

How to Set Round Precision after Calculating Accuracy in Excel

  • An Excel Options window will pop out.
  • Go to the Advanced group.
  • Check the ‘Set precision as displayed’ option from the ‘When calculating this workbook’ section.
  • A Microsoft Excel warning message shows up. Click OK to continue the process.

How to Set Round Precision after Calculating Accuracy in Excel

  • We will decrease the decimal to set rounding precision.
  • Select the cell range E5:E9.
  • Go to the Home tab.
  • From the Number section, select the Decrease Decimal option.

  • We will see the result below. The selected cells are rounded with precision.

Read More: How to Forecast Sales Using Regression Analysis in Excel


Things to Remember

  • If the Actual value is much lower than the Demand value, the function will return a negative or zero accuracy.

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, use the following formula:
=ABS(C5/D5-1)

  • The #DIV/0! Error occurs when the Actual value is 0 like in the screenshot below.

  • 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


Related Articles


<< Go Back to Excel for Finance | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Nuraida Kashmin
Nuraida Kashmin

Nuraida Kashmin, Bachelor's degree in Mechanical Engineering from Rajshahi University of Engineering & Technology. Since 2021, she wrote 45+ articles on Excel problems and reviewed over 1000 articles. Currently working as a Project Manager, she is responsible for developing and implementing content strategies, managing writers and editors, staying updated on new technology, analyzing data, and tracking content performance indicators. Her interests include Project Management, Creative Writing, Digital Marketing, Reporting, Monitoring & Documentation, and Online Advocacy for SAP &... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo