One of the common tasks in business analysis is to measure the performance of a metric against a target or goal. For example, you may want to know how much of your sales quota you have achieved, or how close you are to reaching your budget limit. To do this, you need to calculate the percent of goal, which is the ratio of the actual value to the goal value, expressed as a percentage.
In this article, we will show you how to calculate percent of goal in Excel using different methods. We will also explain the basic theory behind the calculation, and provide a scenario with real data to illustrate the process.
The formula for calculating percent of goal is:
This formula tells us how much of the goal value we have achieved by dividing the actual value by the goal value, and multiplying by 100% to convert the decimal to a percentage. For example, if our actual sales are $80,000 and our goal sales are $100,000, then our percent of goal is:
This means that we have achieved 80% of our sales goal.
Procedures
There are different ways to calculate percent of goal in Excel, depending on how your data is organized and what output you want. Here are some of the common methods:
Method 1: Using a simple formula
The simplest way to calculate percent of goal in Excel is to use a simple formula that references the cells containing the actual value and the goal value. For example, if the actual value is in cell B2 and the goal value is in cell C2, then the formula in cell D2 is:
=B2/C2
This will return the decimal value of the percent of goal, such as 0.8. To format it as a percentage, you can apply the percentage number format to cell D2 by clicking the Percent Style button on the Home tab of the ribbon, or by pressing Ctrl+Shift+5. This will display the value as 80%.
Method 2: Using the QUOTIENT and MOD functions
Another way to calculate percent of goal in Excel is to use the QUOTIENT and MOD functions. The QUOTIENT function returns the integer part of a division, while the MOD function returns the remainder of a division. For example, if the actual value is in cell B2 and the goal value is in cell C2, then the formula in cell D2 is:
=QUOTIENT(B2,C2)&"%"&MOD(B2,C2)/C2*100
This will return the percent of goal as a text value, such as 80%20. The formula works by concatenating the quotient and the remainder of the division, separated by a percentage sign. To format it as a percentage, you can apply the percentage number format to cell D2 as explained in method 1.
Method 3: Using a pivot table
A third way to calculate percent of goal in Excel is to use a pivot table. A pivot table is a powerful tool that allows you to summarize and analyze large amounts of data in a flexible and interactive way. To use a pivot table to calculate percent of goal, you need to have your data in a table format, with columns for the actual value, the goal value, and any other categories or dimensions that you want to analyze. For example, you may have a table like this:
| Month | Actual Sales | Goal Sales |
|---|---|---|
| Jan | 80,000 | 100,000 |
| Feb | 90,000 | 100,000 |
| Mar | 95,000 | 100,000 |
| Apr | 85,000 | 100,000 |
| May | 75,000 | 100,000 |
| Jun | 70,000 | 100,000 |
To create a pivot table from this table, follow these steps:
- Select any cell in the table, and click the Insert tab of the ribbon.
- Click the PivotTable button, and choose where you want to place the pivot table (a new worksheet or an existing worksheet).
- In the PivotTable Fields pane, drag the Month field to the Rows area, the Actual Sales field to the Values area, and the Goal Sales field to the Values area again.
- Right-click any cell in the Sum of Goal Sales column, and choose Show Values As > % of Column Total. This will display the percent of goal for each month, based on the total goal sales for all months.
- Optionally, you can format the pivot table as you like, such as changing the number format, adding filters, slicers, or charts, etc.
Example
To demonstrate how to calculate percent of goal in Excel using the methods explained above, let’s use a scenario with real data. Suppose we are a sales manager for a company that sells software products, and we have a table that shows the actual sales and the goal sales for each product and each quarter of the year 2023, like this:
| Product | Quarter | Actual Sales | Goal Sales |
|---|---|---|---|
| A | Q1 | 120,000 | 150,000 |
| A | Q2 | 130,000 | 150,000 |
| A | Q3 | 140,000 | 150,000 |
| A | Q4 | 160,000 | 150,000 |
| B | Q1 | 80,000 | 100,000 |
| B | Q2 | 90,000 | 100,000 |
| B | Q3 | 95,000 | 100,000 |
| B | Q4 | 85,000 | 100,000 |
| C | Q1 | 60,000 | 75,000 |
| C | Q2 | 65,000 | 75,000 |
| C | Q3 | 70,000 | 75,000 |
| C | Q4 | 80,000 | 75,000 |
We want to calculate the percent of goal for each product and each quarter, and also the overall percent of goal for the whole year.
Method 1: Using a simple formula
To use a simple formula to calculate percent of goal, we can add a new column to the table, and enter the formula =D2/E2 in cell F2, where D2 is the actual sales and E2 is the goal sales. Then, we can copy the formula down to the rest of the column, and apply the percentage number format to the column. The result will look like this:
| Product | Quarter | Actual Sales | Goal Sales | Percent of Goal |
|---|---|---|---|---|
| A | Q1 | 120,000 | 150,000 | 80% |
| A | Q2 | 130,000 | 150,000 | 87% |
| A | Q3 | 140,000 | 150,000 | 93% |
| A | Q4 | 160,000 | 150,000 | 107% |
| B | Q1 | 80,000 | 100,000 | 80% |
| B | Q2 | 90,000 | 100,000 | 90% |
| B | Q3 | 95,000 | 100,000 | 95% |
| B | Q4 | 85,000 | 100,000 | 85% |
| C | Q1 | 60,000 | 75,000 | 80% |
| C | Q2 | 65,000 | 75,000 | 87% |
| C | Q3 | 70,000 | 75,000 | 93% |
| C | Q4 | 80,000 | 75,000 | 107% |
To calculate the overall percent of goal for the whole year, we can use the formula =SUM(D2:D13)/SUM(E2:E13) in cell F14, and format it as a percentage. The result will be 92%, which means that we have achieved 92% of our total sales goal for the year 2023.
Method 2: Using the QUOTIENT and MOD functions
To use the QUOTIENT and MOD functions to calculate percent of goal, we can add a new column to the table, and enter the formula =QUOTIENT(D2,E2)&"%"&MOD(D2,E2)/E2*100 in cell F2, where D2 is the actual sales and E2 is the goal sales. Then, we can copy the formula down to the rest of the column, and apply the percentage number format to the column. The result will look like this:
Table
| Product | Quarter | Actual Sales | Goal Sales | Percent of Goal |
|---|---|---|---|---|
| A | Q1 | 120,000 | 150,000 | 80% |
| A | Q2 | 130,000 | 150,000 | 86%67 |
| A | Q3 | 140,000 | 150,000 | 93%33 |
| A | Q4 | 160,000 | 150,000 | 106%67 |
| B | Q1 | 80,000 | 100,000 | 80% |
| B | Q2 | 90,000 | 100,000 | 90% |
| B | Q3 | 95,000 | 100,000 | 95% |
| B | Q4 | 85,000 | 100,000 | 85% |
| C | Q1 | 60,000 | 75,000 | 80% |
| C | Q2 | 65,000 | 75,000 | 86%67 |
| C | Q3 | 70,000 | 75,000 | 93%33 |
| C | Q4 | 80,000 | 75,000 | 106%67 |
To calculate the overall percent of goal for the whole year, we can use the same formula as in method 1, =SUM(D2:D13)/SUM(E2:E13) in cell F14, and format it as a percentage. The result will be the same as in method 1, 92%.
Method 3: Using a pivot table
To use a pivot table to calculate percent of goal, we can follow the same steps as explained in the procedures section, using the table as the source data. The result will look like this:
Table
| Row Labels | Sum of Actual Sales | Sum of Goal Sales | % of Column Total |
|---|---|---|---|
| A | 550,000 | 600,000 | 92% |
| B | 350,000 | 400,000 | 88% |
| C | 275,000 | 300,000 | 92% |
| Grand Total | 1,175,000 | 1,300,000 | 90% |
The pivot table shows the percent of goal for each product, based on the total goal sales for all products. It also shows the overall percent of goal for the whole year, which is 90%. This is slightly different from the result obtained by methods 1 and 2, because the pivot table uses the column total as the denominator, while the other methods use the row total.
