Calculate two percentages from a single cell

[salbert_54]

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!

[#2 · Accepted solution]

@salbert_54 

=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.

[#3]

@OliverScheurich Yes this is perfect! Thank you for the help!