Forum Discussion
Commission Calculation totals
I should just confirm that your output table is an Excel Table (with or without the jolly stripes). The fact that your field names comprise multiple words makes the syntax slightly more complex, but it should come up correctly simply by clicking the target cell.
= LOOKUP( 1, 1 / ( tblC3[Revenue Type] = [@[Revenue Type]]) / ( tblC3[Outlet] = [@Outlet]), tblC3[Total part] * [@[Total spend]] * tblC3[Commission %] )The thing to note that if an additional square bracket appears, it should have a corresponding closing bracket.
You can solve this much more cleanly by separating the commission rate/structure lookup from the actual commission calculation.
Instead of creating a very large nested IF, I would recommend having a small lookup table for the commission rules and then using XLOOKUP (or INDEX/MATCH) to determine which rule applies based on Rev Type + Outlet.
For example, you could create a helper key:
=J2&"|"&K2
where:
- J2 = Rev Type
- K2 = Outlet
Then your commission rules table could contain keys such as:
| Key | Commission |
|---|---|
| New to TV|TV | 20% |
| New Business|TV | 15% |
| Digital|Digital | 6% |
| Direct|Digital | 13% |
| Agency|TV | Agency Formula |
Your monthly total would simply be:
=SUM(L2:W2)
Then the commission formula can use the appropriate rule.
For a straightforward percentage commission:
=Total*Rate
For example, if the total is $12,000 and the commission is 6%:
=12000*6%
which gives $720.
For an Agency calculation, you can handle the special formula separately rather than trying to force every commission type into the same percentage lookup.
One important point: your examples appear to have a small inconsistency. You wrote:
(.85%*12000)*(.06)
That calculation is $6.12, not $612. If you intended .85 × 12000 × .06, that would be $612. So I would verify whether the agency factor is 0.85, 0.85%, or 85% before building the final formula.
For a spreadsheet with many commission structures, I would also recommend using an Excel Table for your client data and a separate Commission Rules table. That makes it much easier to add or change commission structures later without rewriting formulas.
If you're also working with construction/material calculations, a similar approach—separating inputs, lookup rules, and calculations—is used in tools such as CalcPave: https://calcpave.com/
The key idea is: don't put the entire business logic into one huge nested IF. Store the rules in a table and let the formula retrieve the appropriate rule.