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