Conditional calculation?

Melissa Wittig
edited 12/09/19 in Formulas and Functions

I am trying to create a formula that will calculate sales commission based on which marketer is named in another column.

I have two marketers who are at different commission levels. I would like the Commission column to automatically calculate the commission total from the dollar value in the Services column, based on which marketer is listed in the Marketer column.

Additionally, the commission percentage changes based on whether the Services type was Mitigation or Reconstruction.

The conditions are as follows:

If Marketer = Jerry, and Service Type = Mitigation, then multiply Services by 3% and populate the cell with that total.

If Marketer = Jerry, and Service Type = Reconstruction, then multiply Services by 1.5%

If Marketer = Melissa, and Service Type = Mitigation, then multiply Services by 1%.

If Marketer = Melissa, and Service Type = Reconstruction, then multiply Services by 0.5%.

If Marketer = Team Split, and Service Type = Mitigation, then multiply Services by 4%.

 

Can anyone help me figure out what formulas I need and how to nest them? I hope I asked this in a way that makes sense.Thank you so much!!

Comments

  • Chris McKay
    Chris McKay ✭✭✭✭✭✭
    edited 06/07/18

    Hi Melissa,

    Looks like you're getting a raw deal in terms of commission :-)

    You've structured your question really well and so your challenge was easy to understand.

    To make future admin an easier prospect, instead of hard-coding values in your formulas I'd suggest creating a Services sheet with your commission and service type values. This will allow you to change commission values or add new marketers on the fly without having to edit a long list of formulas. Trust me, it's worth the little extra effort in the long run.

    So if we look at the new sheet, you'd want a Marketer column (you can use the default Primary column), a Service Type column and finally a Value column (ideally formatted as a %). We could have created a column for each service type or each marketer, but again this is easier for future administration management.

    You'll then create a new row with a Marketer, Service Type and Value until you have created a matrix like the below:

    Jerry | Mitigation | 3%

    Jerry | Reconstruction | 1.5%

    Melissa | Mitigation | 1%

    Melissa | Reconstruction | 0.5%

    Team | Mitigation | 4%

    Finally, let's create a Combined column to the right (that can be locked and hidden) with this formula:

    =Marketer@row + " " + [Service Type]@row

    Now, back in your original sheet we'll need to do some work on the sheet structure. Smartsheet does not support multiple lookup values (this would need an array formula), so we'll have to replicate what we had in the Combined column (that can also be locked and hidden) in your Services sheet and add this formula:

    =Marketer@row + " " + [Service Type]@row

    Finally, use the following formula in the Commission column (assuming the existence of MarketerServices and Service Type columns in this sheet):

    =SUM(Services@row * INDEX({Services Value}, MATCH(Combined@row, {Services Combined}, 0)))

    Anything with a curly bracket is a cross-sheet reference and can be specified when you're typing in the formula. It will ask you what sheet you are referencing and you simply need to point it to the Services sheet we created and highlight the relevant column header (so the whole column is selected).

    Voila! You have your commission value and nothing has been hard coded.

    Hope this helps.

    Kind regards,

    Chris McKay

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    While Chris has made a good suggestion (flexibility is almost always a good thing), if you are not worried about flexibility and want to just plug in a formula, you can use the following (see picture for positive testing):

     

    =IF(AND(Marketer@row = "Jerry", [Service Type]@row = "Mitigation"), Services@row * 0.03, IF(AND(Marketer@row = "Jerry", [Service Type]@row = "Reconstruction"), Services@row * 0.015, IF(AND(Marketer@row = "Melissa", [Service Type]@row = "Mitigation"), Services@row * 0.01, IF(AND(Marketer@row = "Melissa", [Service Type]@row = "Reconstruction"), Services@row * 0.005, IF(AND(Marketer@row = "Team Split", [Service Type]@row = "Mitigation"), Services@row * 0.04)))))

     

    If you wanted to add in a commission for "Team Split" and "Reconstruction", you can just add this between the last 4 and the parenthesis. Then you would just have to adjust your percentage.:

     

    , IF(AND(Marketer@row = "Team Split", [Service Type]@row = "Reconstruction"), Services@row * 0.04)

    Untitled.png

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!