Excel formula if number between two values return different values

[pnr8uk]

Hi All

Got myself really stuck with something I should know better.

I have values in column D of my spreadsheet which will range from 2 to 11

I want to write a formula to return a value in column E along the following lines

When Col D cell 3 <= 3.5 then 1
When Col D cell 3 >= 3.6 Or D3 <= 7.4 then 0.6
When Col D cell 3 <= 7.5 or D3 <= 11 then 0.4
I thought it would be simply
IF(AND(D3<=3.5,1,D3>=3.6,D3<=7.4,0.6,D3<=7.5,D3<=11,0.4))

eg: (and values between)
D E
3.5 1
3.6 0.6
7.5 0.4

Any ideas or help greatly appreciated

 

[Aladin Akyurek]

Something like:

=LOOKUP(D3,{0,3.6,7.5,11.01},{1,0.6,0.4,"?"})

 

[Joe4]

I think you made a mistake here:

When Col D cell 3 <= 7.5 or D3 <= 11 then 0.4

I believe you mean:
When Col D cell 3 >= 7.5 or D3 <= 11 then 0.4

You don't need ANDs if you work up or down in order, since in a nested IF, it will stop at the first true clause it finds.
So try:

=IF(D3<=3.5,1,IF(D3<=7.4,0.6,IF(D3<=11,0.4,"not found")))

You dodn't say what to return in greater than 11, so I have it returning "not found". You can change that part to whatever you like.

 

[pnr8uk]

I think you made a mistake here:

I believe you mean:
When Col D cell 3 >= 7.5 or D3 <= 11 then 0.4

You don't need ANDs if you work up or down in order, since in a nested IF, it will stop at the first true clause it finds.
So try:

=IF(D3<=3.5,1,IF(D3<=7.4,0.6,IF(D3<=11,0.4,"not found")))

You dodn't say what to return in greater than 11, so I have it returning "not found". You can change that part to whatever you like.

Thank you so much for this...
That was surprisingly easy, I have made heavy weather of this so many times before.
The column will never be over 11 but this is great thank you so much.
P

 

[Joe4]

You are welcome.
Also take a look at Aladin's solution. It is another way of doing this in a little more concise manner.