Formula syntax

  • Thread starter Thread starter Dale G
  • Start date Start date
D

Dale G

Hi, I'm trying place a formula in cell C3.

IF(AND(K3="","",IF(LEFT(K3)="4",M3="a"),"CTA","STD"),IF(AND(K3="","",IF(LEFT(K3)="5",M3="a"),"STA","Gil"))

I'm trying to have cell C3 show CTA if the number in K3 starts with a 4 and
the letter in M3 is "a". And if the number in K3 starts with a 4 and the
letter in M3 is "c"
have C3 show STD.

Or if the number in K3 starts with a 5, and M3 is "a" have C3 show STA, or
if K3 starts with a 5 and M3 is "c" then C3 would show Gil.

Any help is appreciated.
 
Dale G said:
I'm trying to have cell C3 show CTA if the number
in K3 starts with a 4 and the letter in M3 is "a".
And if the number in K3 starts with a 4 and the
letter in M3 is "c" have C3 show STD.

Or if the number in K3 starts with a 5, and M3 is "a"
have C3 show STA, or if K3 starts with a 5 and M3 is
"c" then C3 would show Gil.
Click to expand...

One way:

=if(left(K3)="4",
if(M3="a", "CTA", if(M3="c", "STD", "")),
if(left(K3)="5",
if(M3="a", "STA", if(M3="c", "Gil", "")), ""))

Alternative:

=if(left(K3)="4", if(M3="a", "CTA", if(M3="c", "STD", "")), "")
& if(left(K3)="5", if(M3="a", "STA", if(M3="c", "Gil", "")), "")

The alternative is less efficienct (infinitesimally), but it is more
extensible because it has less function nesting.

Note: Both formulas can be simplified if you said that M3 will only have
"a" or "c" when left(K3) is "4" or "5", and if left(K3) will ony be "4" or
"5".


----- original message -----
 
One way...

=IF(OR(K3="",M3=""),"",VLOOKUP(LEFT(K3)&M3,{"4A","CTA";"4C","STD";"5A","STA";"5C","GIL"},2,0))

Or, create a little table:

...........A..........B
1.......4A.......CTA
2.......4C.......STD
3.......5A.......STA
4.......5C.......GIL

Then:

=IF(OR(K3="",M3=""),"",VLOOKUP(LEFT(K3)&M3,A1:B4,2,0))
 
Hi, I'm trying place a formula in cell C3.

IF(AND(K3="","",IF(LEFT(K3)="4",M3="a"),"CTA","STD"),IF(AND(K3="","",IF(LEFT(K3)="5",M3="a"),"STA","Gil"))

I'm trying to have cell C3 show CTA if the number in K3 starts with a 4 and
the letter in M3 is "a". And if the number in K3 starts with a 4 and the
letter in M3 is "c"
have C3 show STD.

Or if the number in K3 starts with a 5, and M3 is "a" have C3 show STA, or
if K3 starts with a 5 and M3 is "c" then C3 would show Gil.

Any help is appreciated.
Click to expand...

Try this formula in cell C3:

=IF(LEFT(K3)="4",IF(M3="a","CTA",IF(M3="c","STD","unspecified")),IF(LEFT(K3)="5",IF(M3="a","STA",IF(M3="c","Gil","unspecified")),"unspecified"))

The formula can be made shorter if you can assume that the number in
K3 always starts with either a 4 or a 5 and that M3 always is
either "a" or "c".

In that case, try this formula:

=CHOOSE((LEFT(K3)="5")+2*(M3="c")+1,"CTA","STA","STD","Gil")

But you did not mention anything about that so we have to assume that
there are no such limitations to the data and use a the longer
formula.

Hope this helps / Lars-Åke
 
One more option
=IF(LEFT(K3,1)="4",IF(LEFT(M3,1)="a","CTA",IF(LEFT(M3,1)="c","STD","")),IF(LEFT(K3,1)="5",IF(LEFT(M3,1)="a","STA",IF(LEFT(M3,1)="c","Gil","")),""))
 
Both formulas can be simplified if you said that M3 will only have
"a" or "c" when left(K3) is "4" or "5", and if left(K3) will ony be "4" or
"5".

That statement is true. The letters used will only be "a" Or "c" and the
first number
will always be a 4 or 5.
 
Dale G said:
That statement is true. The letters used will only
be "a" Or "c" and the first number will always be
a 4 or 5.
Click to expand...

Well, your original formula suggests that K3 might also appear blank. So
you might try:

=if(K3="", "",
if(left(K3)="4", if(M3="a", "CTA", "STD"),
if(M3="a", "STA", "Gil")))


----- original message -----
 
I said:
=if(K3="", "",
if(left(K3)="4", if(M3="a", "CTA", "STD"),
if(M3="a", "STA", "Gil")))
Click to expand...

Another alternative:

=LOOKUP(left(K3)&M3, {"","4a","4c","5a","5c"}, {"","CTA","STD","STA","Gil"})

Assumes that M3 appears blank when K3 appears blank.


----- original message -----
 
Yes your right, the cell may be blank.

This works very well.

=IF(K3="", "", IF(left(K3)="4", IF(M3="a", "CTA", "STD"),IF(M3="a", "STA",
"Gil")))

Thanks for your help and thanks to everyone else, all excellent suggestions.
Dave thanks for the comments. Would you have any suggestion on where I might
find reading material (web or books) and or tutorials on how to write
formulas?
 
Back
Top