Shorten Formula

G

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"))
 
J

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

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

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
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Complicated Formula 1
Formula with blank cells 0
Time, & Ceiling? 5
Need LV MASS formula 12
How to determine the value - 15 Dec? 4
someone please shorten this formula... 6
Formula terror 6
If Condition 0

Top