Need help/advice on multiple If Statements

[winderkent]

Happy New Year Everyone,

I am trying to use an IF Statement here; however keep getting Syntax Error. Appreciate if anyone could kindly assist. Thank you

=IF((K7)>=60,(K7)<80),0.02, IF(AND((K7)>=80,(K7)<100),0.035, IF(AND((K7)>=100,(K7)>=120),0.0425,0.05)))

If the result is >=60%, then it will display 2%.
But if the result is >= 80%, then it will display 3.5%
Then if the result is >= 100%, then it will display 4.25%
Then if the result is >= 120%, then it will display 5.00%

 

[JumpingCrab]

This might solve it:

=IF(AND(K7>=60,K7<80),0.02, IF(AND(K7>=80,K7<100),0.035, IF(AND(K7>=100,K7<120),0.0425,IF(K7>=120,0.05,0))))

You appeard to have missed an AND after the first IF. The last IF contained an error as well. You also don't need to enclose the cell references in ()
This might be cleaner though:

=IF(K7>=120,0.05,IF(K7>=100,0.0425,IF(K7>=80,0.035,IF(K7>=60,0.02,0))))

You obviously have to input the number in K7 as a percentage and not as the actual numbers (e.g. 80 and not 0.8). Otherwise change the numbers in the formula accordingly.

 

[Scott T]

What should be returned if it is under 60%?

=IF(K7>=120%,5%,IF(K7>=100%,4.25%,IF(K7>=80%,3.5%,IF(K7>=60%,2%,"under 60%"))))

 

[Nishant94]

You can also try:

=LOOKUP(K7,{0,0;0.6,0.02;0.8,0.035;1,0.0425;1.2,0.05})

 

[winderkent]

What should be returned if it is under 60%?

=IF(K7>=120%,5%,IF(K7>=100%,4.25%,IF(K7>=80%,3.5%,IF(K7>=60%,2%,"under 60%"))))

Will be 0%

 

[Scott T]

Will be 0%

Then change my formula to

[COLOR=#333333]=IF(K7>=120%,5%,IF(K7>=100%,4.25%,IF(K7>=80%,3.5%,IF(K7>=60%,2%,0%))))[/COLOR]

 

[winderkent]

Then change my formula to

[COLOR=#333333]=IF(K7>=120%,5%,IF(K7>=100%,4.25%,IF(K7>=80%,3.5%,IF(K7>=60%,2%,0%))))[/COLOR]

Thank much. It works!