Right and Countif functions

[virfir97]

x-no-archive: yes
Help required.

Qu 1) How do I combine the following two formulas into one formula?
Basically I'm trying to construct one formula that sees if the end of
a string fits a criteria and returns a value if true or false. I've
tried many formulas without success. I know I could nest IF(RIGHT(A1)
=... but is there any other way of constructing one formula?

$B$1 =RIGHT(A1)

$C$1 =IF(SUM(COUNTIF(B1,{0,1,8,9})),"Y","N")


Qu 2) Is it possible to adapt the following formula so that the
criteria is {2,3,6,7} and *without* nesting IF statements? I know the
simple solution is to use countif, I'm just curious. I have a feeling
it isn't possible.

=IF((D1)=2,"",D1)

Thanks in Advance.

 

[Harlan Grove]

(e-mail address removed) wrote...

x-no-archive: yes
Help required.

You may get less help if you insist on setting the x-no-archive tag in
your ng postings to yes. Why not allow your postings to be archived?

Qu 1) How do I combine the following two formulas into one formula?
Basically I'm trying to construct one formula that sees if the end of
a string fits a criteria and returns a value if true or false. I've
tried many formulas without success. I know I could nest IF(RIGHT(A1)
=... but is there any other way of constructing one formula?

$B$1 =RIGHT(A1)

$C$1 =IF(SUM(COUNTIF(B1,{0,1,8,9})),"Y","N")

Try the following in C1, dispensing with B1.

=IF(OR(RIGHT(A1,1)={0,1,8,9}),"Y","N")

Qu 2) Is it possible to adapt the following formula so that the
criteria is {2,3,6,7} and *without* nesting IF statements? I know the
simple solution is to use countif, I'm just curious. I have a feeling
it isn't possible.

=IF((D1)=2,"",D1)

You're wrong. The simple solution is using OR.

=IF(OR(D1={2,3,6,7}),"",D1)

 

[Biff]

Hi!

For q1:

I'm assuming that the value in A1 is either numeric like: 123456789, or
alpha-numeric, like: ABC123

=IF(OR(RIGHT(A1)*1={0,1,8,9}),"Y","N")

For Q2:

Use the same technique:

=IF(OR(D1={2,3,6,7}),"",D1)

Biff

 

[virfir97]

(e-mail address removed) wrote...

You may get less help if you insist on setting the x-no-archive tag in
your ng postings to yes. Why not allow your postings to be archived?

Harlan would you just STFU and mind your own business.

 

[Harlan Grove]

Harlan would you just STFU and mind your own business.

So you don't understand public forums?

You post stupidity here, anyone else gets to point it out and raise the
question whether your flaws are the product of genetics or environment.

 

[David McRitchie]

I don't know who you think would have a better answer than Harlan,
but it makes one wonder if you even looked at the answers he gave
you within thirty minutes of your original post.

Your use of no-archive makes for an interesting situation, especially
since you posted from Google. So Google has to not honor the
request for five days so that people who work with newsgroups
through Google would at least see the question. Of course the
whole purpose of working with newsgroups is so that everyone
gets helped not just you, so the use of no-archive is inappropriate.

Google redid everything so that Google posters would be working from the
archives, and we now have very quick indexing of newsgroup postings.

Threads are to be tied back to the original message-id, and that
original posting is to be deleted.

From a later posting of yours, we can see at the moment what happens, as
Google prefixes the original post with
1. (e-mail address removed) Aug 26, 6:46 am show options
Note: The author of this message requested that it not be archived. This message
will be removed from Groups in 4 days (Sep 2, 6:46 am).
so no one will be able to see the original question later on in the archives
or make heads or tails out of the answers.

http://groups.google.com/group/micr...*excel*+group:*excel*&rnum=1#b1878b87ef4e09a3

To get the most out of newsgroups you would use your name,
thank people for their answers, use appropriate language,
and don't use no-archive, and help others with Excel.

---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm


[clipped]