Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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"))

    Please help!
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

  2. #2
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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!
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    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
    - Ricky

  4. #4
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    Or, this one :

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

    Regards
    Bosco

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
  •