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 ?
Tagged:
0
Best Answer
-
The best approach is what @JaredDolich laid out in this article (link). Please try to stay away from the nasty IF THEN ELSE statements.
Rob
0
Answers
-
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 amount0 -
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 !0
