Assume you have the following dataset. It contains the forecasted demand units and actual demand units for products.
Method 1 – Calculate Accuracy Percentage with Excel ABS Function
- Enter the following formula in the first cell of your Accuracy column.
- Click on the % icon on the Home Ribbon to apply the percentage number format.
- Double-click on the Fill Handle icon or drag it down to apply the formula to the cells below.
Read More: Make an Excel Spreadsheet Automatically Calculate Percentage
Method 2 – Compute Accuracy Percentage with the IF Function
- Enter the following formula in the first cell of your Accuracy column.
=1-IF(C5>D5,(C5-D5)/D5,(D5-C5)/D5)
- Click the %icon on the Home Ribbon to apply the percentage number format.
- Double-click on the Fill Handle icon or drag it down to apply the formula to the cells below.
Method 3 – Use the MIN-MAX Combination to Calculate Accuracy Percentage
- Enter the following formula in the first cell of your Accuracy column.
=1-(MAX(C5:D5)-MIN(C5:D5))/D5
- Click the %icon on the Home Ribbon to apply the percentage number format.
- Double-click on the Fill Handle icon or drag it down to apply the formula to the cells below.
Read More: How to Calculate Win-Loss Percentage in Excel
Errors in Accuracy Percentage Calculations
When the Forecast data is much different than the Demand value, this can result in a negative or zero accuracy percentage.
In this case, calculating the error percentage can be better than figuring the Accuracy Percentage.
- If the Demand value becomes zero, then Excel will show the #DIV/0! Error.
Read More: How to Calculate Error Percentage in Excel
Things to Remember
- You must use the actual value (Demand) in the denominator in the formula while calculating the accuracy percentage.
- Make sure to use parentheses and brackets properly in formulas.
Download Practice Workbook
You can download the practice workbook from the download button below.
Related Articles
<< Go Back to Calculating Percentages| Calculate in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Md. Shamim Reza
Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio
FAQs
What is the Excel formula for percentage? To calculate a percentage in Excel, you can use the formula: "=number/total*100". Replace "number" with the specific value you want to calculate a percentage of and "total" with the overall value or sum. Multiply the result by 100 to get the percentage representation.
How to check the accuracy of data in Excel? ›
Select the cell or cells that you wish to check during entry. On the Data tab, in the Data Tools group, click Data Validation to open the Data Validation dialog box. On the Settings tab, specify the criteria you wish the entered data to meet: Choose the Time data type in the Allow dropdown menu.
How to calculate the accuracy percentage? ›
Percentage Accuracy Formula
The following formula is used to calculate the percentage accuracy of a test. To calculate a percentage accuracy, subtract the observed value from the true value, divide by the true value, multiply by 100, then subtract this result from 100.
How do I find the percentage difference between 3 numbers in Excel? ›
How to Calculate Percent Change in Excel?
- Calculate the Difference Between the Ending and Beginning Value.
- Divide the Difference by the Beginning Value.
- Multiply by 100 to Convert the Decimal into Percentage Form.
(c) Overall accuracy:
It is obtained by dividing the total numbers of correctly classified pixels by the total numbers of reference pixels. The drawback of this measure is that it does not tell us about how well individual classes are classified.
How to calculate data accuracy? ›
You can calculate the accuracy in five steps:
- Calculate the true positives (TP)
- Compute the true negatives (TN)
- Calculate the false positives (FP)
- Estimate the false negatives (FN)
- Apply the accuracy formula: accuracy = (TP + TN) / (TP + TN + FP + FN)
Although Excel allows display of up to 30 decimal places, its precision for any specific number is no more than 15 significant figures, and calculations may have an accuracy that is even less due to five issues: round off, truncation, and binary storage, accumulation of the deviations of the operands in calculations, ...
How to do accuracy and precision in Excel? ›
Use this option only if you are certain that the displayed precision will maintain the accuracy of your data.
- Click File > Options.
- Click Advanced, and then under When calculating this workbook, select the Set precision as displayed check box, and then click OK.
- Click OK.
The forecast accuracy formula is straightforward : just divide the sum of your errors by the total demand.
What is the top 5 accuracy formula? ›
Top-5 accuracy means any of our model's top 5 highest probability answers match with the expected answer. It considers a classification correct if any of the five predictions matches the target label. In our case, the top-5 accuracy = 3/5 = 0.6.
How to measure data accuracy? 11 Essential steps
- Define the ideal state.
- Data profiling.
- Validation against trusted sources.
- Error rate calculation.
- Sample testing and spot checks.
- Feedback loops.
- Automated data validation tools.
- Consistency checks.
To determine the percentage, we have to divide the value by the total value and then multiply the resultant by 100.
How to calculate percentage of 3 values? ›
To calculate the percentage of a number out of the total number, just use the formula number / total number × 100. An increase or decrease in any quantity can be expressed as a percentage. This is referred to as percentage change. Fractions can be converted into percentages and vice-versa.
How to calculate percentage of 3 numbers in Excel? ›
Percentages are calculated by using the equation amount / total = percentage. For example, if a cell contains the formula =10/100, the result of that calculation is 0.1. If you then format 0.1 as a percentage, the number will be correctly displayed as 10%.
How to calculate percentage difference between 3 numbers? ›
To calculate this we use the formula: (difference of the values)(mean of the values)×100= Percentage difference.
How to calculate accuracy ratio in Excel? ›
To calculate AR of the grouped data, switch to the GROUPED worksheet and enter the formula =QRS.DISC.AR(B2:B8, D2:E8) in cell F9. The result is -0.797, which is similar to the AR of the ungrouped data. The TEST , LABELS , and TRANSPOSE options can be used as before.
How do I show exact percentage in Excel? ›
Display numbers as percentages
in the Number group on the Home tab, or press Ctrl+Shift+%. If you want more control over the format, or you want to change other aspects of formatting for your selection, you can follow these steps.
How do I calculate true and false percentage in Excel? ›
You can use the following formula to calculate the percentage of yes or no values in a range of cells in Excel: =COUNTIF (B5:AF5, "Yes")/COUNTA (B5:AF5) This formula will count the number of cells that contain the value "Yes" in the range B5:AF5, and then divide it by the number of cells that contain any value in the ...
How to calculate forecast accuracy percentage? ›
Mean Absolute Percentage Error (MAPE) is a common method for calculating sales forecast accuracy. It's calculated by taking the difference between your forecast and the actual value, and then dividing that difference by the actual value.
