Forum Discussion
Commission Calculation totals
Hello! I am working on a spreadsheet for calculating my call list and commissions. I am having trouble because there are different commission structures based on the rev type and outlet also, agency has a complicated formula in its self.
Example:
Rev Type=Direct
Outlet= Digital
Commission=.06%
Total spend= $12,000
Equals= $720
Example 2:
Rev Type=Agency
Outlet= TV
Commission=(.85%*Total spend)*(.06%)
Total spend= $12,000
Equals= (.85%*12,000)*(.06)=$612
So depending on the Rev Type and the Outlet I have a different commission structure. I want to be able to put in what ever my client is spending monthly, total that up and then have the commission calculate the correct formula pending the rev type and the outlet type.
Rev Types:
| New to TV | 20% |
| New Business | 15% |
| Digital | 6% |
| Direct | 13% |
| Agency | (.85*x10)*(.06) |
Outlet Types:
| TV |
| Digital |
| MeTV |
I have tried using the Nested IF, VLookup, and XLookup but I am having no luck!
Here is the table I am looking to work with:
| First Name | Last Name | Phone | Status | Last Follow Up | Notes | Stage | Rev Type | Outlet | January | February | March | April | May | June | July | August | September | October | November | December | Total | Commission | ||
| Direct | TV | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 12000 |
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.
25 Replies
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.
From the sample it's not clear how Commission depends on Outlet type. Do you have any formal logic?
- Hi Sergei,
yes so depending on the rev type and outlet depends on the commission percentage. Below is the breakdown
Revenue Type Outlet Comission % Comments
New to TV WDSU 20%
New Business WDSU 15%
Direct WDSU 13%
Agency WDSU ((.85*total)*(.06)) Formula to get to the percentage
New Business MeTV 20%
Direct MeTV 13%
New Agency MeTV ((.85*total)*(.10)) Formula to get to the percentage
Agency ((.85*total)*(.6)) Formula to get to the percentage
Digital (total8*06) Formula to get to the percentageThank you.
- If Outlet is missed does that mean formula is for any Outlet?
- What is the latest formula?
