Partial Formula Working

Paul.Woodward

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)

jmyzk_cloudsmart_jp

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.