Excel If help

[smartie91]

Hi All

I need help with a excel if statement please what i have is a list containing machine ids

T003478

N000299

T003067

TT90044

all the above are what we call FM only the first to chars should be used as there are over 2000 ids

the second digit is a zero

NR02297

TX00024

TR02175

TC12345

these are called SF

there for if C1 = T0 then print FM or c1=nr print SF

i have this so far

=IF((C1="TT"),FM,IF((C1="N0"),FM,IF((C1="NR"),SF,IF((C1="TR"),SF,"NONE"))))

and the error i get is

#NAME?	TT90185
NONE	T003478
#NAME?	NR02297
NONE	TX00024

hope this make sence

thanks

Nigel

edit

I have got it to work if i only put 2 char in c1 useing this

=IF((E5="TT"),"FM",IF((E5="N0"),"FM",IF((E5="NR"),"SF",IF((E5="TR"),"SF",IF((E5="T0"),"FM",IF((E5="TX"),"SF","NONE"))))))

how do i no only pick first 2 chars

also i need altogether 10 if statement i think excel only does 7 is that right

thanks

nigel

Edited June 19, 2008 by smartie91

[IcemanND]

I won't redo the whole line but you should get the idea:

=if (Left(C1,2)="TT","FM",if(Left(C1,2)="NR","SF","None"))

[smartie91]

Thanks IcemanND

I have this now

=IF(LEFT(G4,1)="F","CYLYPSO",IF(LEFT(G4,2)="TX","SF",IF(LEFT(G4,2)="N0","FM",IF(LEFT(G4,2)="TT","FM",IF(LEFT(G4,2)="T0","FM",IF(LEFT(G4,2)="TR","SF",IF(LEFT(G4,2)="NR","SF","CCM")))))))

which works fine But the problem is now i cant search from this column

A ..... B .......C.........D.......... E......... F

SF 753651 Address Name 2A TX00085

FM 787621 Address Name 2B NR03955

CCM 792373 Address Name 2C NR03024

SF 794187 Address Name 3B NR00127

Column A is the result of above

I need to display how many SF,FM,CCM, there are in 2A,2B.2C.

thanks for your help

Nigel

[jaclaz]

Wouldn't a "count if" function do?

http://www.ozgrid.com/Excel/count-if.htm

jaclaz