Excel formula if number between two values return different values

pnr8uk

New Member
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
 

Joe4

MrExcel MVP, Junior Admin
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:
Code:
=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

New Member
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:
Code:
=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

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

Some videos you may like

This Week's Hot Topics

Top