 SOLVED

# Calculate two percentages from a single cell

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 Replies
best response confirmed by salbert_54 (New Contributor)
Solution

# Re: Calculate two percentages from a single cell

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

# Re: Calculate two percentages from a single cell

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