# Thread: if value between 2 numbers... (2003)

1. ## if value between 2 numbers... (2003)

Ok, I'm an idiot...I can't figure out the formula for this.

I need a formula that says if the value in a cell is between 122 & 142, then show AT.
If between 143 & 153, show OT.
If less than 122 or above 153, show OT

I tried this, but it does not appear to work.
=IF(122<=E8<142,"AT",IF(143<=E8<=153,"OT","BT"))

2. ## Re: if value between 2 numbers... (2003)

Well, I rethought it and went with this:

=IF(E8<122,"BT",IF(E8<=142,"AT",IF(E8<=153,"OT","B T")))

Still curious if there is a better way to do the inbetween...

Thanks!

3. ## Re: if value between 2 numbers... (2003)

Here's one way:
<pre><font color=blue>=IF(AND(E8>122,E8<142),"AT",IF(AND(E8>1 43,E8<153),"OT","BT"))</font color=blue> </pre>

HTH

4. ## Re: if value between 2 numbers... (2003)

Or, this one :

=LOOKUP(E8,{0;122;143;154},{"BT";"AT";"OT";"BT"})

Regards
Bosco

5. ## Re: if value between 2 numbers... (2003)

I would use that one, or create a lookup table:

<table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>1</td><td align=right>-9.99999999999999E+307</td><td>BT</td><td align=center>2</td><td align=right>122</td><td>AT</td><td align=center>3</td><td align=right>143</td><td>OT</td><td align=center>4</td><td align=right>153</td><td>BT</td></table>
and use VLOOKUP:

=VLOOKUP(E8,\$A\$1:\$B\$4,2)

This is similar to bosco_yip's solution but on the one hand less clever, on the other hand easier to maintain.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•