I am trying to use the following IF statement, which I want to look at a cell & return varying percentage amounts depending on the amount in the cell being looked at.
Ex. if F4 contains 10795, I want F5 to reflect 0.05; if F4 contains 75000, I want F5 to reflect 0.03, etc...
I keep getting an error when I attempt to use this IF statement. Any ideas? Is it the "AND" causing the problem? How else I can make it return the desired amount for the ranges needed?
My complete list of ranges & desired percentages is as follows, but since the IF statement must be limited to 7, I eliminated the 1st set:
1-5000 = 0.07 (or minimum 500)
5001-10000 = 0.06
10001-25000 = 0.05
25001-35000 = 0.04
35001-40000 = 0.035
40001-75000 = 0.03
75001-150000 = 0.025
150001-UP = 0.02
=IF(F4>5000AND<10001,0.06,IF(F4>10000AND<25001,0.05,IF(F4>25000AND<35001,0.04,IF(F4>35000AND<40001,0.035,IF(F4>40000AND<75001,0.03,IF(F4>75000AND<150001,0.025,IF(F4>150000,0.02)))))))
Ex. if F4 contains 10795, I want F5 to reflect 0.05; if F4 contains 75000, I want F5 to reflect 0.03, etc...
I keep getting an error when I attempt to use this IF statement. Any ideas? Is it the "AND" causing the problem? How else I can make it return the desired amount for the ranges needed?
My complete list of ranges & desired percentages is as follows, but since the IF statement must be limited to 7, I eliminated the 1st set:
1-5000 = 0.07 (or minimum 500)
5001-10000 = 0.06
10001-25000 = 0.05
25001-35000 = 0.04
35001-40000 = 0.035
40001-75000 = 0.03
75001-150000 = 0.025
150001-UP = 0.02
=IF(F4>5000AND<10001,0.06,IF(F4>10000AND<25001,0.05,IF(F4>25000AND<35001,0.04,IF(F4>35000AND<40001,0.035,IF(F4>40000AND<75001,0.03,IF(F4>75000AND<150001,0.025,IF(F4>150000,0.02)))))))