Ready To Use Excel Formulas : how to calculate percentage of goal.

Formulas are the true engines of Excel. Employing various Excel functions, formulas

enable Excel analysts to create aggregated reporting, complex calculation engines,

clever dashboard models, and much more. Indeed, Excel analysts become more

productive as their proficiency with Excel functions and formulas improves.

But building proficiency with Excel functions and formulas takes time. Given that

Excel contains more than 400 functions, you could spend months, even years, learning

which functions are best for certain tasks and which functions can be combined with

others functions.

Unfortunately, many analysts don’t have the luxury of taking a few weeks’ time-out to

learn all they need to know about Excel functions and formulas. The scenarios and

issues they face require solutions now.

Formula 1: 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).

Calculating the percentage of a Goal

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.

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.

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 Figure 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

Calculating the Percentage of a 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.

Read also Learn Microsoft Excel lesson #41