IF number between or is greater than etc.,

[pnr8uk]

I have been going crazy to try and solve something which I think seems fairly simple.

In one Cell i.e AF3 this cell can receive any value but I want the adjacent AE2 to display one of a list of values based on the value in cell AF3

For instance if the value in AF3 is >= 250 then the adjacent cell AE2 should display "BIG WIN"
if the value in AF3 is > 100 but < than 250 than the adjacent cell AE2 should display "WIN"
if the value in AF3 is < 100 but < than 50 then the adjacent cell AE2 should display "SMALL WIN"

etc., for about 6 more ranges.

I have tried =IF(AF3>=250, "BIG WIN",0) which works of course
but then if I add IF (AF3>=250(AND < .... things just start to go wrong and I don't get the variations I need.
I tried VLOOKUP with the values defined in another cell.
I tried IF(AND( etc., but I can only get so far.

I have Googled a lot but there seems to be no way to achieve this kind of... if this is and this is then... but if this is and this is then.

Any help as always greatly received.

KR

Paul

 

[Michael M]

How about this
start with the lowest win first and increment up from there

=IF(AF3<100,"SMALL WIN",IF(AF3<250,"WIN","BIG WIN"))

 

[Taul]

Hi,
Try this approach
You can adjust the numbers to suit.

=LOOKUP(AF3,{0,50,100,250},{"nil","Small Win","Win","Big Win"})

cheers
Paul.

 

[pnr8uk]

Thank you both for your replies both were very helpful I have gone with Taul's solution eventually as it give me more flexibility. Thanks again both.
Paul

 

[pnr8uk]

Hi,
Try this approach
You can adjust the numbers to suit.

=LOOKUP(AF3,{0,50,100,250},{"nil","Small Win","Win","Big Win"})

cheers
Paul.

Many thanks for this, how would I get it work if the value was -250, -100 so the check is for the values quoted which works perfectly but I also want to include negative numbers too.

 

[Taul]

Hi,
Try this.
Basically keep the same number of items in each of the sets of curly brackets, in this case there are eight items in each set.
You may need to change the -250 to -249 depending on where you want it to switch over.

=LOOKUP(AF3,{-1000,-250,-100,-50,0,50,100,250},{"whooped","Big Loss","Loss","Small Loss","nil","Small Win","Win","Big Win"})

 

[pnr8uk]

What an absolute gent. Thank you for your help. Best wishes
Paul