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.

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)

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.

Method 2 – Calculate Accuracy with the Excel ABS Function

Steps:

Select Cell E5.
Insert the following code:

=1-ABS(C5/D5-1)

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

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.

Select Options.

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.

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.

Things to Remember

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

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

Calculate Accuracy and Precision.xlsm

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

Get FREE Advanced Excel Exercises with Solutions!