Formula Excel : Calculating Percent of Goal

When someone asks you to calculate a percent of goal, she is simply saying to
compare actual performance to a stated goal. The math involved in this calculation is
simple: Divide the goal by the actual. This gives you a percentage value that
represents how much of the goal has been achieved. For instance, if your goal is to
sell 100 widgets, and you sell 80, your percent of goal is 80 percent (80/100).

For sample all formula you can download here.

 Note : Percent of Goal can also be referred to as percent of budget or percent
of forecast; it all means the same thing.

In Image 2-1, you see a list of regions with a column for goals and a column for
actuals. Note that the formula in cell E5 simply divides the value in the Actual column
by the value in the Goal column.

=D5/C5

Image 2-1: Calculating the percent of goal.

How it works

There isn’t much to this formula. You’re simply using cell references to divide one value by another. You just enter the formula one time in the first row (cell E5 in this case) and then copy that formula down to every other row in your table. Alternative: Using a common goal

If you need to compare actuals to a common goal, you can set up a model like the one shown in Image 2-2. In this model, each region does not have its own goal. Instead, you’re comparing the values in the Actual column to a single goal found in cell B3.

=C6/$B$3

Image 2-2: Calculating the percent of goal using a common goal.

Note that the cell reference to common goal is entered as an absolute reference ($B$3). Using the dollar symbols locks the reference to the goal in place, ensuring that the cell reference pointing to your common goal does not adjust as you copy the formula down.

For more information on absolute and relative cell references see article about Tips Excel : Relative versus Absolute Cell References.

Categories: Formula