A goal tile that only shows percent of target hides whether you are actually on schedule: 40% of the annual goal is great in March and terrible in November. This recipe compares progress to the share of the period that has passed and labels each goal Ahead, On pace or Behind with a tolerance band.
$52,000 of a $120,000 goal after 120 of 365 days is 43.3% of goal versus 32.9% of the year, so it is Ahead.
The example
Three goals at different points in the year. The same percent complete reads differently depending on elapsed time.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Case | Goal | Actual | Days elapsed | Total days | Pace | % of goal |
| 2 | Sales | 120,000 | 52,000 | 120 | 365 | Ahead | 43.3% |
| 3 | Leads | 80,000 | 30,000 | 150 | 365 | Behind | 37.5% |
| 4 | Revenue | 50,000 | 21,000 | 150 | 365 | On pace | 42.0% |
The formula
Expected progress is days elapsed over total days; the flag compares actual progress to it:
How it works
Here is the logic, one piece at a time:
C2/B2is the percent of goal reached.D2/E2is the share of the period elapsed, which is the expected progress at constant pace.- Multiplying the expected by 1.05 and 0.95 forms the tolerance band.
- The nested
IFreturns Ahead, On pace or Behind.
For seasonal goals, replace the straight-line expected value with a seasonality curve.
Try it: interactive demo
Enter the goal, actual to date and days elapsed.
Variations
Change the tolerance
Put the tolerance in a cell so teams can set their own band.
Color the flag
Use conditional formatting to fill Ahead green and Behind red.
Pitfalls & errors
A goal of zero returns #DIV/0!; guard it with IF(B2=0,"No goal",...).
Straight-line pacing assumes even results across the period, which rarely holds for sales.
Show elapsed percent next to the flag so viewers see what On pace means.
Practice workbook
Frequently asked questions
Can I use months instead of days?
Does the order of the IF tests matter?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class