Formula syntax

[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.

 

[Joe User]

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.

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

 

[T. Valko]

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

 

[Lars-Åke Aspelin]

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.

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

 

[JLatham]

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

 

[David Biddulph]

Difficult to know where to start in commenting on your formula.
First, the pairs of parentheses must match. Yours don't, so the formula
won't be accepted by Excel..
Secondly, you twice have an AND function in which the second argument is the
empty text string "", and the 3rd and 4th arguments are also text strings.
The AND function expects boolean inputs, not text, so will return a #VALUE!
error.
Thirdly, your second IF function has only one argument, the AND function
defining the condition. You haven't given the IF function aqny definition
of what you want the result to be, weither if the condition is satisfied or
if it isn't.

So let's start again and look at what you want the formula to do and deal
with it a stage at a time. The first argument of an IF function is the
condition, the second argument is the result if that condition is satisfied,
and the third argument is the result if the condition is not satisfied. You
can nest the functions as required, and often the result if the first
condition is not satisfied would be to do a second test, hence a second IF
function.

How does this look?
=IF(LEFT(K3)="4",IF(M3="a","CTA",IF(M3="c","STD","result undefined as wrong
M3")),IF(LEFT(K3)="5",IF(M3="a","STA",IF(M3="c","Gil","result undefined as
wrong M3")),"result undefined as wrong K3"))

 

[Dale G]

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.

 

[Joe User]

That statement is true. The letters used will only
be "a" Or "c" and the first number will always be
a 4 or 5.

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

 

[Joe User]

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

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

 

[Dale G]

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?