Spreadsheet Formula: Excel / Google Sheets
Build a formula for our sales commission calculation sheet
No card required
What this brief specifies
The sheet layout
Column B holds total ACV closed this quarter, Column C holds quota attainment percentage, and Column D holds a three-tier commission rate: 5% up to 99%, 8% from 100 to 150%, 12% above that.
Tool
This needs to work in Excel 365.
Edge cases
The formula has to survive a rep with zero sales, missing data in any of the input columns, and attainment over 300%.
What makes this hard
This isn't a flat percentage formula, it's a three-tier rate structure, 5%, 8%, and 12%, keyed off a second column's quota attainment percentage, which already needs nested conditional logic rather than one multiplication. The edge cases stack on top of that structure specifically: a rep with zero sales sits at the bottom of tier one and has to resolve to zero commission cleanly, not an error or a negative number; missing data in any input column has to fail safely instead of silently producing a wrong number that looks plausible; and a rep past 300% attainment has to land in the top, uncapped tier rather than breaking the boundary logic. The tier boundaries themselves, exactly at 99 to 100 and 150 to 151, are where a small formula bug becomes an expensive one, because a boundary error in a commission sheet doesn't just look wrong on screen, it pays somebody the wrong amount of real money.
Frequently asked questions
The next tiered-rate calculation that has to survive a zero, a gap in the data, and an outlier without silently miscalculating benefits from a formula built against those boundary cases from the first draft, rather than one patched after someone downstream notices their payout is off.