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

N

NTWyatt

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

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
 
A

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

how do I get a result if one cell is X and the 2nd cell is Y where 6
Need Help with Excel and Flagging Outcomes! 4
Problem with Function 12
IF function not working 3
Using If function for Great and less than function 6
i need help with a function application with multiple arguments 3
Returning value based on input data 2
Satisfying Multiple Criteria in an "IF" Function 1

Top