Nested Ifs for Commission Calculation

ZachK

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 ?

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 !