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