Need an advanced "if" function: if 2<x<2.9 then y, if 3<x<3.9 then
 

Hi there - I need help with getting a formula (single cell if possible) that
will apply the following table:

Less than 3.5 to 1.00
Level I 4%
Less than 4.0 to 1.00, but greater than or equal to 3.5 to 1.00 Level II
4.5%
Less than 5.0 to 1.00, but greater than or equal to 4.0 to 1.00 Level
III 5%
Less than 6.0 to 1.00, but greater than or equal to 5.0 to 1.00 Level IV
5.5%
Greater than 6.0 to 1.00
Level V 6%

Thanks in advance!

Need an advanced "if" function: if 2<x<2.9 then y, if 3<x<3.9 then
 

=HLOOKUP(A1,{0,3.5,4,5,6;0.04,0.045,0.05,0.055,0.0 6},2)*(AND(A10,A1<10))

assuming the value is in A1 and in your question by 1.0 you really meant 10
otherwise clarify what does "Greater than 6.0 to 1.00" means



"NTWyatt" wrote in message
...
Hi there - I need help with getting a formula (single cell if possible)
that
will apply the following table:

Less than 3.5 to 1.00
Level I 4%
Less than 4.0 to 1.00, but greater than or equal to 3.5 to 1.00 Level
II
4.5%
Less than 5.0 to 1.00, but greater than or equal to 4.0 to 1.00 Level
III 5%
Less than 6.0 to 1.00, but greater than or equal to 5.0 to 1.00 Level
IV
5.5%
Greater than 6.0 to 1.00
Level V 6%

Thanks in advance!

Need an advanced "if" function: if 2<x<2.9 then y, if 3<x<3.9 then
 

Hi

=CHOOSE(MATCH(A1,{0;3.5;4;4.5;5;5.5;6},1),0.04,0.0 45,0.05,0.055,0.6)
, or
=0.04+0.005*INT(2*MIN(MAX(A1,3),6)-3)


Arvi Laanemets


"NTWyatt" wrote in message
...
Hi there - I need help with getting a formula (single cell if possible)
that
will apply the following table:

Less than 3.5 to 1.00
Level I 4%
Less than 4.0 to 1.00, but greater than or equal to 3.5 to 1.00 Level
II
4.5%
Less than 5.0 to 1.00, but greater than or equal to 4.0 to 1.00 Level
III 5%
Less than 6.0 to 1.00, but greater than or equal to 5.0 to 1.00 Level
IV
5.5%
Greater than 6.0 to 1.00
Level V 6%

Thanks in advance!