Unexpected Result with "If" Logic/Conditional Statement

[Guest]

Hi,

The worksheet I'm working on now has been pulled together from a number of
different sources (each with their own issues) into what is supposed to be a
comprehensive mailing list.

I wrote the statement below to try to deal with some of that inconsistency
in the Zip Code field -- some entries have five digits, some nine, while
others are either incomplete or entered as text. I formatted the column so
with the "Special" Zip format, but I need to account for leading zeroes,
and/or truncated (5-digit) Zips.

For the most part this formula works, but for some reason, I'm getting
leading zeroes appended to the text entries (CANADA/FOREIGN) in addition to
the numeric ones. Does anyone know why, and/or have a suggestion on how to
adapt this formula to do what I'd like it to do? Any and all assistance
would be greatly appreciated. Thanks.

Formula is as follows:

=IF(LEN(TRIM(CLEAN(K150)))=0,"",(IF(LEN(TRIM(CLEAN(K150)))=5,TRIM(CLEAN(K150))&"0000",IF(AND(9>(LEN(TRIM(CLEAN(K150))))>5,(OR(TRIM(CLEAN(K150))<>"CANADA",TRIM(CLEAN(K150))<>"FOREIGN"))),(REPT("0",(9-(LEN(TRIM(CLEAN(K150))))))&TRIM(CLEAN(K150))),TRIM(CLEAN(K150))))))

 

[Bill Ridgeway]

Hi,

The worksheet I'm working on now has been pulled together from a number of
different sources (each with their own issues) into what is supposed to be
a
comprehensive mailing list.

I wrote the statement below to try to deal with some of that inconsistency
in the Zip Code field -- some entries have five digits, some nine, while
others are either incomplete or entered as text. I formatted the column
so
with the "Special" Zip format, but I need to account for leading zeroes,
and/or truncated (5-digit) Zips.

For the most part this formula works, but for some reason, I'm getting
leading zeroes appended to the text entries (CANADA/FOREIGN) in addition
to
the numeric ones. Does anyone know why, and/or have a suggestion on how
to
adapt this formula to do what I'd like it to do? Any and all assistance
would be greatly appreciated. Thanks.

Formula is as follows:

=IF(LEN(TRIM(CLEAN(K150)))=0,"",(IF(LEN(TRIM(CLEAN(K150)))=5,TRIM(CLEAN(K150))&"0000",IF(AND(9>(LEN(TRIM(CLEAN(K150))))>5,(OR(TRIM(CLEAN(K150))<>"CANADA",TRIM(CLEAN(K150))<>"FOREIGN"))),(REPT("0",(9-(LEN(TRIM(CLEAN(K150))))))&TRIM(CLEAN(K150))),TRIM(CLEAN(K150))))))

I'm not going to try to analyse that one. A solution I've found to work
with enigmatic formula is to break out each of the IF statements into
separate lines (several if an AND or OR is involved) and, with test data,
check the actual outcome with the expected outcome. This is sometimes the
way I construct complicated formula.

Hope this helps!

Bill Ridgeway
Computer Solutions

 

[joeu2004]

I'm not going to try to analyse that one. A solution I've found to work
with enigmatic formula is to break out each of the IF statements into
separate lines

Agreed. I have also had some success with the Excel Evaluate Formula
tool. Select the cell, then click on Tools >> Formula Auditing >>
Evaluate Formula.

(But sometimes EF does not reveal everything we need to know, and
Bill's suggestion is the better alternative.)

 

[joeu2004]

For the most part this formula works, but for some reason, I'm getting
leading zeroes appended to the text entries (CANADA/FOREIGN) in\
addition to the numeric ones.
[....]
Formula is as follows:
=IF(LEN(TRIM(CLEAN(K150)))=0,"",(IF(LEN(TRIM(CLEAN(K150)))=5,TRIM
(CLEAN(K15­0))&"0000",IF(AND(9>(LEN(TRIM(CLEAN(K150))))>5,(OR(TRIM
(CLEAN(K150))<>"CANA­DA",TRIM(CLEAN(K150))<>"FOREIGN"))),
(REPT("0",(9-(LEN(TRIM(CLEAN(K150))))))­&TRIM(CLEAN(K150))),
TRIM(CLEAN(K150))))))

I am not taking the time to parse and understand the above, and the
following might not address your specific question. But I do notice
one glaring syntax error that will be troublesome at least in __some__
cases. You wrote:

AND(9>(LEN(TRIM(CLEAN(K150))))>5,
(OR(TRIM(CLEAN(K150))<>"CANA­DA",
TRIM(CLEAN(K150))<>"FOREIGN")))

"9>LEN(...)>5" does not do what you intended. I think you want:

AND(9>LEN(...), LEN(...)>5, OR(TRIM(...)<>"CANADA",
TRIM(...)<>"FOREIGN")

PS: I don't know why you bother to check the length. It seems
sufficient just to compare with the expected text, "CANADA" and
"FOREIGN".

FYI, I think it would be easier for you (and everyone else) to read
and maintain if you eliminated unnecessary parentheses. For example,

(REPT("0",(9-(LEN(TRIM(CLEAN(K150))))))&TRIM(CLEAN(K150)))

can be written more simply as:

REPT("0", 9-LEN(TRIM(CLEAN(K150)))) & TRIM(CLEAN(K150))

 

[Guest]

Thank you both for responding -- I realize that this statement was reasonably
complex, and definitely wasn't the most readily intuitive, but that's the
main reason why I turned here for help when I found myself stuck.

With that said, I made sure to use both the Evaluate Formula tool and the
statement deconstruction technique to ensure that each part of the formula
works exactly as I intended it (which, thankfully, it does, with the sole
exception of the "and/or" combination). Unfortunately, while I was able to
use the two resolution techniques to confirm my suspicions as to where the
problem lied, neither was actually able to help pin-point the exact nature of
the problem or how to fix it.

Luckily, after taking the time to think about it some more over the holiday,
I was finally able to see that the statement was doing exactly what it
supposed to do, based on the way I wrote it -- the problem wasn't in the
statement, but rather, in my logic (i.e., the "OR" statement shouldn't exist
at all, but rather be included as additional terms in the "AND" statement).

Specifically, if the number of characters in the cell are greater than five
and less than nine, and the exact character string in the cell is not equal
to "CANADA" <b><i>and</i></b> (rather than "OR") not equal to "FOREIGN", then
the formula should pre-fix the characters (in this case, the only character
strings that are left over are comprised of numeric digits) with as many
zeroes as necessary to set the number of characters in the cell equal to 9
(dashes do not count, as I had previously removed them all).The reason this
works is because I want all cases of the character strings other than the
text strings to be appended with leading zeroes.

Now I realize that what I had written before caused the "OR" statement to
trigger the "REPT" function unintentionally, because every time it was true
for one of the text phrases (e.g., "CANADA"), it had to be false for the
other, and yet nonetheless, given the nature of the "OR" statement, it was
returning a total "True" value, which the preceding "AND" statement used to
initiate the "REPT" trigger.

In any case, thank you both, again, for your time and your suggestions -- I
definitely appreciate it, and couldn't have understood the problem without
them.

-- A Need to Excel

PS: Being that there were only two text exceptions in this column, I didn't
mind writing them into the formula, but in the interest of maintaining
manageability and scalability, do either of you (or anyone else for that
matter), know of a function in excel which will dictate the type of
characters used in that cell? Specifically, is there anyway that I could
write something such as "If the characters of the cell are only text, then
do..., otherwise, if the characters of the cell are only numeric, then
do..."? I'd also be curious to know if there is a function which identifies
the contents of a cell as alphanumeric. Thanks.

For the most part this formula works, but for some reason, I'm getting
leading zeroes appended to the text entries (CANADA/FOREIGN) in\
addition to the numeric ones.
[....]
Formula is as follows:
=IF(LEN(TRIM(CLEAN(K150)))=0,"",(IF(LEN(TRIM(CLEAN(K150)))=5,TRIM
(CLEAN(K15-0))&"0000",IF(AND(9>(LEN(TRIM(CLEAN(K150))))>5,(OR(TRIM
(CLEAN(K150))<>"CANA-DA",TRIM(CLEAN(K150))<>"FOREIGN"))),
(REPT("0",(9-(LEN(TRIM(CLEAN(K150))))))-&TRIM(CLEAN(K150))),
TRIM(CLEAN(K150))))))

I am not taking the time to parse and understand the above, and the
following might not address your specific question. But I do notice
one glaring syntax error that will be troublesome at least in __some__
cases. You wrote:

AND(9>(LEN(TRIM(CLEAN(K150))))>5,
(OR(TRIM(CLEAN(K150))<>"CANA-DA",
TRIM(CLEAN(K150))<>"FOREIGN")))

"9>LEN(...)>5" does not do what you intended. I think you want:

AND(9>LEN(...), LEN(...)>5, OR(TRIM(...)<>"CANADA",
TRIM(...)<>"FOREIGN")

PS: I don't know why you bother to check the length. It seems
sufficient just to compare with the expected text, "CANADA" and
"FOREIGN".

FYI, I think it would be easier for you (and everyone else) to read
and maintain if you eliminated unnecessary parentheses. For example,

(REPT("0",(9-(LEN(TRIM(CLEAN(K150))))))&TRIM(CLEAN(K150)))

can be written more simply as:

REPT("0", 9-LEN(TRIM(CLEAN(K150)))) & TRIM(CLEAN(K150))