Table of Contents
Calculating percent error in Excel is a simple and efficient way to determine the accuracy of a measurement or calculation. To do so, subtract the actual value from the experimental or estimated value, divide that result by the actual value, and then multiply by 100 to get the percent error. This can easily be done using the “=” formula in a cell or by creating a formula using the “IF” and “ABS” functions. By following these steps, one can quickly and accurately determine the percentage of error in their data, allowing for a better understanding of the accuracy of their results.
Calculate Percent Error in Excel (With Example)
Percent error is the difference between a measured value and a known or accepted value.
You can use the following formula to calculate percent error:
Percent error = |(known value – measured value) / known value| * 100
The larger the percent error, the greater the difference between a measured value and a known value.
As an example, suppose the height of some tree is known to be exactly 30 feet but you measure it yourself and declare it to have a height of 29.4 feet.
The percent error of your measurement would be calculated as:
- Percent error = |(known value – measured value) / known value| * 100
- Percent error = |(30 – 29.4) / 30| * 100
- Percent error = .02 * 100
- Percent error = 2%
The percent error between the known height of the tree and your measured height of the tree is 2%.
The following example shows how to use a formula in Excel to calculate percent error.
Example: Calculate Percent Error in Excel
To calculate percent error in Excel, you must type the known value in one cell and your measured value in another cell.
For example, we will type our known height of 30 in cell A2 and our measured height of 29.4 in cell B2:
We can then type the following formula into cell C2 to calculate the percent error of our measurement:
=ABS((A2-B2)/A2)*100
The following screenshot shows how to use this formula in practice:
If you would like to display the percentage symbol in the result, you can type the following formula into cell C2 instead:
=ABS((A2-B2)/A2)*100
This will return .02 as a result:
You can then select cell C2 and then click the Number Format dropdown menu within the Number group on the Home tab along the top ribbon and then click Percentage:
This will display the result of the formula in a percentage format:
Additional Resources
The following tutorials explain how to perform other common operations in Excel: