Nested Ifs for Commission Calculation


Hello i would like to set a basic if nested formula for estimating let's say commissions . If my limits are in units :

0-300 commission 0.2

301-500 commission 0.25

700 and above commission 0.30


Whats the correct syntax :

(if Units Sold>=700,0.30,if(Units Sold>=500,0.25,if(Units Sold>=300,then 0.2,"Zero")))
whats the mistake here please ? any help ?

Best Answer


  • anirudh
    I would not recommend nested ifs as a solution; but here's the answer:

    IF Units Sold > 0 AND Units Sold < 301 THEN 0.2 ELSE IF Units Sold >= 301 AND Units Sold < 501 THEN 0.25 ELSE IF Units Sold >= 700 THEN 0.30 ELSE 0

    The better option is to put this in a list and perform a lookup based on the amount
  • ZachK
    Hi Anirudh , thanks for replying back . I agree, i was thinking as a second approach to this the Vlookup function formula. i will practise this as well! Really appreciate it !