1. ## 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. ## 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. ## 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. ## 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. ## 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
•