Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Darkest Kent, UK., Kent, England
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    IF...OR...RIGHT (Excel '97)

    This is driving me up the wall...
    All I want is for a cell to display a letter Q if true and keep it blank if false, depending on the value of another cell.

    Here's what works:

    =IF(OR($H$9="0203", $H$9="0206", $H$9="0209", $H$9="0212"),"Q","")

    Now what I'd like to do is change the above so that I won't have to change it each year-end (e.g. "0203" is March 2002). I've been trying to create a formula using the RIGHT function that will look at the value of $H$9 and if it ends in "3", "6", "9" or "12", write "Q" as the cell value. Here's what I've tried, with no success:

    =IF(OR(RIGHT($H$9,1)="3"), (RIGHT($H$9,1)="6"),(RIGHT($H$9,1)="9"),(RIGHT($H$ 9,2)="12"),"Q","")

    =IF(RIGHT(($H$9,1)="3"),(($H$9,1)="6"),(($H$9,1)=" 9"),(($H$9,2)="12"),"Q","")

    =IF(RIGHT(($H$9,1)="3" or "6" or "9"),"Q","")



    This is really starting to annoy me now. <img src=/S/scream.gif border=0 alt=scream width=15 height=15>

  2. #2
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IF...OR...RIGHT (Excel '97)

    Try something along the lines of

    =IF(RIGHT($H$9,1)="3","q",IF(RIGHT($H$9,1)="2","q" ,""))

    However, that might be misleading if you are looking for "12". You will have to modify it to search for 6, 9, etc.

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IF...OR...RIGHT (Excel '97)

    You might also think about this worksheet formula that pulls the last digit in directly without the IF

    =IF($H$9>0,"Q " & RIGHT($H$9,1),"")

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

    Re: IF...OR...RIGHT (Excel '97)

    Perhaps the formula

    =IF(MOD(RIGHT($H$9,2),3)=0,"Q","")

    does what you want. It checks whether the last two digits from $H$9 are divisible by 3.

  5. #5
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Darkest Kent, UK., Kent, England
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IF...OR...RIGHT (Excel '97)

    Gentlemen,

    Thanks for the reponses. The Dutchman's formula wins Hans down though.

    (pun intended, and pretty good it was too if I do say so myself.) <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15>

Posting Permissions

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