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

[Guest]

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!

 

[N Harkawat]

=HLOOKUP(A1,{0,3.5,4,5,6;0.04,0.045,0.05,0.055,0.06},2)*(AND(A1>0,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

 

[Arvi Laanemets]

Hi

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


Arvi Laanemets