How to calculate accuracy in Microsoft Excel

In Excel, accuracy refers to the degree to which a calculated or measured value is close to the true value. The accuracy of a calculation or measurement can be determined by comparing it to a known or expected value. In Excel, accuracy is important because it helps ensure that the results of calculations and analyses are reliable and trustworthy.

In this tutorial, we will learn how to calculate accuracy in Microsoft Excel. The standard formulae to calculate accuracy in excel is ( 1 – Total Error). After calculating the decimal value accuracy percentage can be obtained by changing the cell format to “percentage” format. We can also use the ABS and IF functions in calculating accuracy to counter negative errors.

Currently, we have a data set showing actual sales and predicted sales for some months. We will calculate the error for each value by dividing the predicted sales value by the actual sales value.

Method 1: Calculating Accuracy by Standard Accuracy Formulae

Step 1 – Select a Blank Cell and Place an Equals Sign

• Select a blank cell where you want to calculate the accuracy.
• Place an equals sign in the cell.

Step 2 – Use the Standard Formulae

• The syntax of formulae to calculate accuracy will be: 

             1 – ( C2/B2)

• Where C2 is the predicted value and B2 is the actual value.

Step 3 – Press the Enter Key

• A decimal value for accuracy will be returned.

Step 4 – Change the Cell Format to Percentage Format

• Change the format of the cell to a percentage format.
• This can be done by clicking on the percentage ( % ) symbol in the numbers section of the Home tab.
• We can also use CTRL+SHIFT+%.

Step 5 – Use Autofill to Calculate Accuracy for Each Value

• Use Autofill to calculate accuracy for each sales value in the adjacent cells.

Method 2: Using the ABS Function

Step 1 – Select a Blank Cell and Place an Equals Sign

• Select a blank cell where you want to calculate the accuracy.
• Place an equals sign in the cell.

Step 2 – Use the ABS function in the formulae

• The syntax of the formulae will become

            ABS(1-(C2/B2))

• Where C2 is the predicted value and B2 is the actual value.

Step 3 – Press the Enter Key

• A decimal value for accuracy will be returned.

Step 4 – Change the Cell Format to Percentage Format

• Change the format of the cell to a percentage format.
• This can be done by clicking on the percentage ( % ) symbol in the numbers section of the Home tab.
• We can also use CTRL+SHIFT+%.

Step 5 – Use Autofill to Calculate Accuracy for Each Value

• Use Autofill to calculate accuracy for each sales value in the adjacent cells.

Method 3: Using the IF Function in the Formulae

Step 1 – Select a Blank Cell and Place an Equals Sign

• Select a blank cell where you want to calculate the accuracy.
• Place an equals sign in the cell.

Step 2 – Use the IF Function in the Formulae

• The syntax will become:

             IF(C2<B2 , 1-(C2/B2), 1-(B2/C2))

• Where the first argument is the logical test i.e. C2<B2.
• The second and the third arguments are the “if_true_value” and the “if_false_value” respectively.

Step 3 – Press the Enter Key

• A decimal value for accuracy will be returned.

Step 4 – Change the Cell Format to Percentage Format

• Change the format of the cell to a percentage format.
• This can be done by clicking on the percentage ( % ) symbol in the numbers section of the Home tab.
• We can also use CTRL+SHIFT+%.

Step 5 – Use Autofill to Calculate Accuracy for Each Value

• Use Autofill to calculate accuracy for each sales value in the adjacent cells.