Good day: I am using the following formula to make choices for conditional
formatting in other cells. As it is, the "else" would be the result of an
entry of a US state abbreviation and would result in a 2 being placed in the
cell. The problem is that if the source cell is blank a 2 is returned. Is
there another way of expressing this so that a Canadian province abb =1, a US
state = 2 and a blank cell ="" null.
=IF(OR(C6={"NB","NS","NF","NL","PE","PQ","QE","ON" ,"MB","SK","AB","BC"}),1,2)
--
Thanks Kevin

kevin wrote...
Good day: I am using the following formula to make choices for conditional
formatting in other cells. As it is, the "else" would be the result of an
entry of a US state abbreviation and would result in a 2 being placed in
the
cell. The problem is that if the source cell is blank a 2 is returned. Is
there another way of expressing this so that a Canadian province abb =1, a
US
state = 2 and a blank cell ="" null.
=IF(OR(C6={"NB","NS","NF","NL","PE","PQ","QE","ON" ,"MB","SK","AB","BC"}),1,2)
--

Hi,

I think you are looking for this:

=IF(ISBLANK(C6), "",
IF(C6={"NB","NS","NF","NL","PE","PQ","QE","ON","MB ","SK","AB","BC"},1,2))

(watch the line wrap),

Blank...""
Cdn...1
US...2

HTH

Regards,
Kevin

Thanks very much Kevin this is exactly what i needed.

Thanks Kevin

"Kevin H. Stecyk" wrote:

kevin wrote...
Good day: I am using the following formula to make choices for conditional
formatting in other cells. As it is, the "else" would be the result of an
entry of a US state abbreviation and would result in a 2 being placed in
the
cell. The problem is that if the source cell is blank a 2 is returned. Is
there another way of expressing this so that a Canadian province abb =1, a
US
state = 2 and a blank cell ="" null.
=IF(OR(C6={"NB","NS","NF","NL","PE","PQ","QE","ON" ,"MB","SK","AB","BC"}),1,2)
--

Hi,

I think you are looking for this:

=IF(ISBLANK(C6), "",
IF(C6={"NB","NS","NF","NL","PE","PQ","QE","ON","MB ","SK","AB","BC"},1,2))

(watch the line wrap),

Blank...""
Cdn...1
US...2

HTH

Regards,
Kevin

kevin wrote...
Thanks very much Kevin this is exactly what i needed.

Thanks Kevin

My pleasure.

Regards,
Kevin