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)
Best Answer
-
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
.
- This part catches all cases greater than
- If
Answers
-
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
.
- This part catches all cases greater than
- If
-
THANK YOU SO MUCH!!!!!
-
Happy to help!😁
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.1K Get Help
- 450 Global Discussions
- 155 Industry Talk
- 505 Announcements
- 5.4K Ideas & Feature Requests
- 85 Brandfolder
- 156 Just for fun
- 80 Community Job Board
- 514 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!