Percent of goal

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 E5 is:

=D5/C5

The results in column E 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.

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: