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

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.

salbert_54's avatar
salbert_54
Copper Contributor
Feb 23, 2022

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

Resources