Feb 22 2022 12:48 PM
Hello,
I'm trying to calculate commission based on the chart below. I have the following ifs function added =IFS(D5>9999999, 0.06*C5, D5>4999999, 0.14*C5, "TRUE", 0.1*C5). However if a value in column D reaches one of the commission thresholds ($5,000,000, $10,000,000; or $15,000,000) I want the formula to calculate the difference between the threshold to calculate the two commission values. In other words, in the example below, the $5,000,000 commission threshold is reached. I want the $493,203 (D12-E12) * .14 and $46,797 (C12 - number above) * .1. The formula also needs to adapt so if the threshold is met in July or September, the formula calculates in the new row. Thanks in advance for your help!
Feb 22 2022 03:11 PM
Solution=IFS(D5>9999999,
IF(D4>9999999,C5*0.06,(D5-10000000)*0.06+(C5-(D5-10000000))*0.14),
D5>4999999,
IF(D4>4999999,C5*0.14,(D5-5000000)*0.14+(C5-(D5-5000000))*0.1),
"TRUE",
0.1*C5)
Is this what you are looking for? The calculation for the $15,000,000 threshold can be added accordingly with the respective percentage.
Feb 23 2022 06:42 AM
@OliverScheurich Yes this is perfect! Thank you for the help!
Feb 22 2022 03:11 PM
Solution=IFS(D5>9999999,
IF(D4>9999999,C5*0.06,(D5-10000000)*0.06+(C5-(D5-10000000))*0.14),
D5>4999999,
IF(D4>4999999,C5*0.14,(D5-5000000)*0.14+(C5-(D5-5000000))*0.1),
"TRUE",
0.1*C5)
Is this what you are looking for? The calculation for the $15,000,000 threshold can be added accordingly with the respective percentage.