Exceljet

Quick, clean, and to the point

Percent of goal

Excel formula: Percent of goal
Generic formula 
=actual/goal
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 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:

=(actual-goal)/ABS(goal)+100%
Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.