How to calculate percent of goal in Excel

by

If you need to calculate percent of goal, you can do so with a simple formula that divides the actual by the goal amount, with the result formatted using the percentage number format.

This same formula can be used to calculate things like percent of target, percent of budget, percent of forecast, and so on.

Worked Example:   Find, Trace and Correct Errors in Excel Formulas using 'Formula Auditing'

Formula

=actual/goal

Explanation

In the example, we are calculating percent of Forecast, so we simply need to divide the actual result in column D with the Forecast in column C and format the result in column E as using the Percentage number format.

In cell E5, the formula is:

=D5/C5

How this formula works

When working manually, percentage results are calculated by dividing one number by another (for example, a part by a whole) to get a decimal value, then multiplying by 100 to express as a percentage.

Worked Example:   Floating Point Errors in Excel

Excel can display decimal or fractional values automatically as a percentage by applying the Percentage number format, so there is no need to multiply by 100.

Worked Example:   How To Create Budget in Excel

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%

Leave a Reply

Your email address will not be published. Required fields are marked *