SOLVED

Commission Calculation totals

Occasional Contributor

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 
24 Replies

@kds81596 

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 percentage

@Sergei Baklan 

This should be easier 

kds81596_0-1640714247369.png

 

@kds81596 

Thank you.

- If Outlet is missed does that mean formula is for any Outlet? 

- What is the latest formula?

image.png

@Sergei Baklan 

This is more basic (with filled in outlets)

kds81596_0-1640715426881.png

This is what I wish to calculate if you would be able to help with this it would be preferable:

kds81596_1-1640715826474.png

 

@kds81596 

Your formulas have the same structure. I'd add one more column to do all calculations in one form.

However, back to your initial sample I don't see such combinations in commissions table.

image.png

@kds81596 

Formula could be as

=XLOOKUP(
  1,
  ( tblC[Revenue Type] = [@[Revenue Type]] ) *
  ( tblC[Outlet] = [@Outlet] ),
  tblC[Total part] * [@[Total Spend]] * tblC[Comission %] )

where tblC is the table with Commission parameters

image.png 

I'd recommend to use structured tables, not ranges.

Please see in attached.

@Sergei Baklan 

I am trying to type it in but the outlet, total part, and commission isn't taking. What am i doing wrong? 

 

 

@kds81596 

The objects you mention are the Headers applied to columns of the Table, which provide the basis for Structured References.  The shortcut for creating a structured reference to a field is to hover over the top border of the header and click when the black down-arrow shows.  

If this is not working, the first question must be "have you converted the array to a Table (Ctrl/T)?"

If that is OK, have you named the Table (Table Design ribbon tab and the Table Name appear at the extreme left)?

@Peter Bartholomew 

This is all way over my head lol. I have no idea what I am doing and I have tried looking at videos but it's not working for me.

@kds81596 

Have you downloaded Sergei's workbook.  Is that working as you require?  I note it uses XLOOKUP so requires Excel 365 or 2021.  Without that, the formula could be changed to

=LOOKUP(
  1,
  1 / ( tblC[Revenue Type] = [@[Revenue Type]] )
    / ( tblC[Outlet] = [@Outlet] ),
  tblC[Total part] * [@[Total Spend]] * tblC[Comission %] )

since the very old LOOKUP function ignores the #DIV0! errors created by the formula.

Meanwhile use F1 to bring up help and search for structured references.  It is worth the effort to learn about structured references because they offer a far more precise way of referencing data.

@Peter Bartholomew 

I think where I am having trouble is when I copy the formula and paste it into my sheet it doesn't capture anything past the (tblC[Revenue Type]. 

@kds81596 

Don't forget that  tblC[Revenue Type] will only be meaningful to Excel if you have a table called tblC and it has a header Revenue Type.  If not, it is back to the help pages.

@Peter Bartholomew

 

i thought i already had that. I have no idea how to attach a workbook on here or else I would show you. 

 

@Peter Bartholomew 

 

So I got it to work but now the formula will not copy down. The cells match up but i keep getting #N/A

kds81596_0-1640734653006.png

 

@kds81596 

Progress!  If you convert the result array to a Table as well, the references like L20 should be replaced by structured references [@Name] with "@" indicating a single cell.  The formula should propagate down the column automatically.

@Peter Bartholomew 

 

Good Morning! Thank you again for helping me! 

 

It's odd because if I put the bracket [@[Name] it wont highlight the section that I need. The only way I was able to get that one cell to work was by using this: 

=LOOKUP(
1,
1 / ( tblC3[Revenue Type] =L20 )
/ ( tblC3[Outlet] =M20 ),
tblC3[Total part] * Z20 * tblC3[Commission %] )

kds81596_0-1640787437482.png

 

Here is what happens when I put in the [@[Name]:

kds81596_1-1640787558662.png

 

best response confirmed by kds81596 (Occasional Contributor)
Solution

@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.

@Peter Bartholomew 

It wont let me even highlight the cell that I need to high light. Is there any way I can email this to you? I can't figure out how to attach it to this forum. Unless you can help me with that, then I will e happy to do that as well.