If statement problem

[jgold20]

=IF(OR(A4="WL"),("0"),0) +
IF(OR(A4="BL",A4="DL",A4="LL",A4="LGL",A4="NL",A4="MDL",A4="RL",A4="WGL",A4="XL",
A4="A",A4="B",A4="CB",A4="D",A4="K",A4="V",A4="X",
A4="H",A4="L",A4="LG",A4="M",A4="MD",
A4="N",A4="Q",A4="R",A4="T",A4="W",A4="WG",A4="Y"),(A7*B7),0)

In the above statement, only BL, DL, H, LGL, MD, MDL, NL and X are performing properly. All of the others do not return any results. Any suggestions?

 

[steve the fish]

What are you trying to do here? The first if does nothing. The 2nd if will produce a7 * b7 if A4 is any of those letter combinations or 0 if not.

 

[jgold20]

What are you trying to do here? The first if does nothing. The 2nd if will produce a7 * b7 if A4 is any of those letter combinations or 0 if not.

The first if statement is so the field can be blank. The second if is only producing results for only some of them

 

[jgold20]

The first If for WL is to produce a blank result. As I stated on my initial post, A7 * B7 is only occurs for BL, DL, H, LGL, MD, MDL, NL and X.

 

[steve the fish]

The first statement doesnt do anything bar adding zero to zero or zero to the multiplication so in other words nothing. Anyway how is A4 being populated? Are you sure these values that you think dont work are actually 'LL' for example? There are no other characters in there? Trailing spaces? Invisible characters? Its not possible for only some of those to work whilst others do not.

 

[jgold20]

A4 is being populated from a drop down box.

 

[steve the fish]

Ok so take a value that doesnt work. eg LL. Put LL in the dropdown. Then what does this return:

=A4="LL"

Then this:

=CLEAN(TRIM(A4))="LL"

 

[jgold20]

I used A and it returned nothing. Where should I put the =clean - only as the formula?

 

[jgold20]

=CLEAN(TRIM(A4))="A" the return was true - I removed everything else from the formula

 

[jgold20]

=A4="A" returned false
=CLEAN(TRIM(A4))="A" returned true