Explanation
In this example, the objective is to calculate a percentage for each goal shown in column C of the table using the actual values in column D. In other words, given a goal of 100000, and an actual amount of 112000, we want to return 112% as the result. The general formula for this calculation, where “x” is the percentage achieved is:
x=actual/goal
x=112000/100000
x=1.12
Converting this to an Excel formula with cell references, the formula in E5 becomes:
=D5/C5
=112000/100000
=1.12
=112%
As the formula is copied down, the formula returns a decimal number for each city in the list. When these numbers are formatted with the Percentage number format , they are displayed as percentages.
Formatting percentages in Excel
In mathematics, a percentage is a number expressed as a fraction of 100. For example, 25% is read as “Twenty-five percent” and is equivalent to 25/100 or 0.25. Accordingly, the values in column E are decimal values , with the Percentage number format applied.
Negative goal
If you have a negative goal, the formula above won’t calculate correctly. In this case, you can calculate the variance as explained here , then add the variance to 100% to get the percent of goal:
=(actual-goal)/ABS(goal)+100%
Explanation
In this example, the goal is to answer the question “What percentage of students were absent from each class”. In other words, given a class with 30 students total, 27 of which were present, we want to return 10% absent. The general formula for this calculation, where “x” is the percent absent is:
x=absent/total
However, since we don’t have a column for the number of students absent in the table, we need to calculate this number as part of the formula:
x=(total-attended)/total
x=(30-27)/30
x=3/30
x=0.10
After we convert this to an Excel formula with cell references, the formula in E5 becomes:
=(C5-D5)/C5
=(30-27)/30
=3/30
=0.10
As the formula is copied down, the formula returns the calculated “percent absent” for each class listed in the table. These results are decimal numbers formatted with the Percentage number format .
Formatting percentages in Excel
In mathematics, a percentage is a number expressed as a fraction of 100. For example, 55% is read as “Fifty-five percent” and is equivalent to 55/100 or 0.55. To display values with a percent sign (%), apply Percentage number format .
