Summary
To calculate the percentage of a goal attained, you can use a simple formula that divides the actual by the goal amount, with the result formatted using the percentage number format. In the example shown, the formula in cell F5 is:
=D5/C5
The results in column F are decimal values with the percentage number format applied. This same formula can be used to calculate things like percent of target, percent of budget, percent of forecast, and so on.
Generic formula
=actual/goal
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 F5 becomes:
=D5/C5
=112000/100000
=1.12
=112%
As the formula is copied down, the formula returns a decimal number for each office in the list. When these numbers are formatted with the Percentage number format, they are displayed as percentages. Results over 100% mean the goal was exceeded. If you don't want results to go over 100%, see Cap percentage at 100.
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%
For example, with a goal of -100 (a loss of 100) and an actual of -50, the simple formula =actual/goal returns 50%, even though the loss was smaller than planned. The formula above returns 150%.
