Shorten Formula

[George]

Excel 2009
Is there any way I can shorten this formula?
=IF(OR(D3="6A",D3="3G",D3="4G",D3="5G",D3="6G",D3="6L",D3="SS",D3="3T",D3="4T",D3="5T",D3="6T",D3="NIL"),"",IF(D3="3A",C3*E2,"Not
equal to 6A or 3G or 4G or 5G or 6G or 6L or SS or 3T or 4T or 5T or 6T or
NIL"))

 

[Joel]

Is this any better?

=IF(ISNA(MATCH(D3,{"6A","3G","4G","5G","6G","6L","SS","3T","4T","5T","6T","NIL"},0)),IF(D3="3A",C3*E2,"Not
equal to 6A or 3G or 4G or 5G or 6G or 6L or SS or 3T or 4T or 5T or 6T or
NIL"),"")

 

[T. Valko]

Create this defined name:

Insert>Name>Define
Name: Codes
Refers to:

={"6A","3G","4G","5G","6G","6L","SS","3T","4T","5T","6T","NIL"}

OK

Then:

=IF(ISNA(MATCH(D3,Codes,0)),IF(D3="3A",C3*E2,"Not equal to 6A, 3G, 4G, 5G,
6G, 6L, SS, 3T, 4T, 5T, 6T or NIL"),"")





Then:

 

[Jarek Kujawa]

don't know much about Excel 2009

but in previous versions the following procedure would work:

in an unused range of a worksheet make a list of 6A, 3G, 4G,..., NIL
etc.

select that range, give it a name (Insert->Name->Define in Excel 2003)
by inserting a name "thelist" in name manager

the yr formula might be like:

=IF(COUNTIF(thelist,D3)>0,"",IF(D3="3A",C3*E2,"Not equal to 6A or 3G
or 4G or 5G or 6G or 6L or SS or 3T or 4T or 5T or 6T or > NIL"))

HIH

 

[Jarek Kujawa]

....

presume "thelist" is H1:H12
in I1 insert
="Not equal to "&H1&" or "&H2

then in I2 insert
=I1&" or "&H2

and copy down to I12

finally yr formula might look like;

=IF(COUNTIF(thelist,D3)>0,"",IF(D3="3A",C3*E2,I12))

HIH