Forum Discussion

salbert_54's avatar
salbert_54
Copper Contributor
Feb 22, 2022
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!

 

 

excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Feb 22, 2022

    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.

2 Replies

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Feb 22, 2022

    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.

Resources