Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to calculate attainment against a quota in Excel and Google Sheets
Excel & Google Sheets
=B2/A2✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Quota | Actual |
| 100000 | 87500 |
=B2/A2→0.875
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1In A2 enter the quota (target) for the period and in B2 enter the actual results. Use the same unit — dollars, units, or calls — for both.
- 2In C2 enter =B2/A2 and format as a percentage. Values above 100% indicate overachievement; below 100% indicate at-risk performance.
- 3For a team view, put each rep in a separate row (A2:B10) and copy the formula down column C. Add a team total: =SUM(B2:B10)/SUM(A2:A10) for correctly weighted overall attainment.
- 4Guard against zero quotas with =IFERROR(B2/A2,"") or =IF(A2=0,"",B2/A2) — a rep with no quota set would otherwise show as infinite attainment.
- 5Apply conditional formatting to column C: red below 80%, yellow 80-99%, green >=100% for immediate visual triage.
Tips
- Average the individual percentages only when quotas are equal across reps. When quotas differ, the weighted formula =SUM(actuals)/SUM(quotas) gives the correct team attainment.
- Track overachievement (>100%) separately — many comp plans accelerate commission above quota, so lumping it with on-target performance loses important payroll data.
- For year-to-date attainment, always cumulate actuals and quotas separately (=SUM each), then divide — do not average the monthly percentages.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
Should attainment be measured on booked, invoiced, or recognized revenue?
Match whatever the quota was set against. Mixing metrics — quota on bookings, actuals on revenue — produces misleading attainment numbers and should be avoided.
How do I rank reps by attainment and break ties?
Use RANK on the attainment column, then add a COUNTIFS tiebreaker on absolute dollar amount — reps who hit a higher dollar amount break ties over those with the same percentage.