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:

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

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

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

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.

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

• 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

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

<< Go Back to Excel for Finance | Learn Excel