If, then formula with multiple options

[laura_pink]

Hi,

I need a formula that will return a value to me based on the content of two cells.

Cell A1 contains a number between 0 and 7 to 2 decimal places i.e. 2.5
Cell A2 contains the words 'OK' or 'NOT POSSIBLE'

I need a formula that says that if cell A2 contains 'NOT POSSIBLE', then the answer is always "3E" regardless of what is in A1. If however A2 contains 'OK' and A1 has a value of 1.9 or less the answer is "1", a value of 2.0-2.9 the answer is "2", a value of 3.0-3.9 the answer is "3", a value of 4.0 or more the answer is "3E"

After many hours trying I cannot get anything to work and am now doubtful as to whether there are too many arguments. Any help would be greatly appreciated.
Thanks, Laura

 

[Fluff]

How about

=IF(OR(A2="NOT POSSIBLE",A1>4),"3E",INT(A1))

 

[cogswel__cogs]

Hi,
Will this do
=IF(B2="Not Possible","3E",ROUNDDOWN(A2,0))

 

[cogswel__cogs]

OOPS sorry should have refreshed first

 

[laura_pink]

How about

=IF(OR(A2="NOT POSSIBLE",A1>4),"3E",INT(A1))

Absolutely brilliant thank you and working perfectly. So clever!

 

[laura_pink]

Hi,
Will this do
=IF(B2="Not Possible","3E",ROUNDDOWN(A2,0))

This worked although I needed the maximum level to be 3E if 4 or higher and this kept putting 5, 6 whatever the digit number started with. I think it was very close though! thanks

 

[Fluff]

Absolutely brilliant thank you and working perfectly. So clever!

You're welcome & thanks for the feedback.

 

[cogswel__cogs]

OOOPS
Always go with "Fluff" thats my moto anyway.