Shorten Formula
 

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="N IL"),"",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"))

Shorten Formula
 

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*E 2,"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"),"")

"George" wrote:

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="N IL"),"",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"))

Shorten Formula
 

Create this defined name:

InsertNameDefine
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:





--
Biff
Microsoft Excel MVP


"Joel" wrote:

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*E 2,"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"),"")

"George" wrote:

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="N IL"),"",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"))

Shorten Formula
 

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


On 27 Lut, 15:27, George wrote:
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"))

Shorten Formula
 

....

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


On 1 Mar, 11:30, Jarek Kujawa wrote:
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

On 27 Lut, 15:27, George wrote:



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"))- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -