Partial Formula Working

Hello, Everyone.

I have a commission sheet with many columns, and I have a 3 part formula to calculate commission based on 3 different % depending on which month of the contract the sales team is in. The first two IF statements work beautifully. The 3rd part I need to calculate that if the Month of Contract is greater than the Contract Length + 12 (months), then use the 12+ Month Contract Commission %. I have a tight deadline so any help would be appreciated. Here is the formula below. The bold part of the formula is not working.

=IF([Month of Contract]@row <= [Contract Length]@row, [Contract Amount]@row * [Initial Contract %]@row, IF([Month of Contract]@row > [Contract Length]@row, [Contract Amount]@row * [After Initial Contract %]@row, IF([Month of Contract]@row > [Contract Length]@row + 12, [Contract Amount]@row * [12+ Month Contract %]@row, 0)

Tags:

Best Answer

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp Community Champion
    edited 03/09/25 Answer ✓

    Hi @Paul.Woodward

    The third IF condition ([Month of Contract]@row > [Contract Length]@row + 12) is never reached because the second IF already catches all cases where [Month of Contract]@row > [Contract Length]@row.

    For example:
    If the Contract Length is 12 months and the Month of Contract is 15, it meets the condition [Month of Contract]@row > [Contract Length]@row but does not meet [Month of Contract]@row > [Contract Length]@row + 12. The formula should apply the "After Initial Contract %" rate in this case.

    However, if the Month of Contract is 25, it does meet both conditions ([Month of Contract]@row > [Contract Length]@row and [Month of Contract]@row > [Contract Length]@row + 12). The formula should apply the "12+ Month Contract %" rate in this case.

    So, bring the IF([Month of Contract]@row > [Contract Length]@row + 12, before, and [Month of Contract]@row > [Contract Length]@row will be treated as the "else" case if not.

    =IF([Month of Contract]@row <= [Contract Length]@row, 
        [Contract Amount]@row * [Initial Contract %]@row, 
        IF([Month of Contract]@row > [Contract Length]@row + 12, 
            [Contract Amount]@row * [12+ Month Contract %]@row, 
            [Contract Amount]@row * [After Initial Contract %]@row
        )
    )
    

    In the Corrected Formula:

    • If [Month of Contract]@row <= [Contract Length]@row → Apply Initial Contract %.
    • Else, if [Month of Contract]@row > [Contract Length]@row + 12 → Apply 12+ Month Contract %.
      • This condition will be reached if the contract month is more than 12 months past the contract length.
    • Else → Apply After Initial Contract %.
      • This part catches all cases greater than [Contract Length]@row but less than [Contract Length]@row + 12.

Answers

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp Community Champion
    edited 03/09/25 Answer ✓

    Hi @Paul.Woodward

    The third IF condition ([Month of Contract]@row > [Contract Length]@row + 12) is never reached because the second IF already catches all cases where [Month of Contract]@row > [Contract Length]@row.

    For example:
    If the Contract Length is 12 months and the Month of Contract is 15, it meets the condition [Month of Contract]@row > [Contract Length]@row but does not meet [Month of Contract]@row > [Contract Length]@row + 12. The formula should apply the "After Initial Contract %" rate in this case.

    However, if the Month of Contract is 25, it does meet both conditions ([Month of Contract]@row > [Contract Length]@row and [Month of Contract]@row > [Contract Length]@row + 12). The formula should apply the "12+ Month Contract %" rate in this case.

    So, bring the IF([Month of Contract]@row > [Contract Length]@row + 12, before, and [Month of Contract]@row > [Contract Length]@row will be treated as the "else" case if not.

    =IF([Month of Contract]@row <= [Contract Length]@row, 
        [Contract Amount]@row * [Initial Contract %]@row, 
        IF([Month of Contract]@row > [Contract Length]@row + 12, 
            [Contract Amount]@row * [12+ Month Contract %]@row, 
            [Contract Amount]@row * [After Initial Contract %]@row
        )
    )
    

    In the Corrected Formula:

    • If [Month of Contract]@row <= [Contract Length]@row → Apply Initial Contract %.
    • Else, if [Month of Contract]@row > [Contract Length]@row + 12 → Apply 12+ Month Contract %.
      • This condition will be reached if the contract month is more than 12 months past the contract length.
    • Else → Apply After Initial Contract %.
      • This part catches all cases greater than [Contract Length]@row but less than [Contract Length]@row + 12.
  • Paul.Woodward
    Paul.Woodward ✭✭✭✭✭

    THANK YOU SO MUCH!!!!!

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp Community Champion

    Happy to help!😁

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!