Forum Discussion

kds81596's avatar
kds81596
Copper Contributor
Dec 28, 2021

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 TV20%
New Business 15%
Digital 6%
Direct13%
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 Email Status Last Follow Up NotesStage Rev Type OutletJanuary February March April May June July AugustSeptember OctoberNovember December Total Commission
         DirectTV10001000100010001000100010001000100010001000100012000 
Formulas and Functions
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Dec 29, 2021

    kds81596 

    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.

    • kds81596's avatar
      kds81596
      Copper Contributor
      Dec 28, 2021
      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 percentage

Resources