Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula syntax
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula syntax
"Dale G" wrote:
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 ----- "Dale G" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula syntax
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)) -- Biff Microsoft Excel MVP "Dale G" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula syntax
On Sun, 24 Jan 2010 08:00:01 -0800, Dale G
wrote: Hi, I'm trying place a formula in cell C3. IF(AND(K3="","",IF(LEFT(K3)="4",M3="a"),"CTA","ST D"),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","S TD","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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula syntax
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","")),"")) "Dale G" wrote: 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula syntax
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","G il","result undefined as wrong M3")),"result undefined as wrong K3")) -- David Biddulph "Dale G" wrote in message ... 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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula syntax
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" wrote: "Dale G" wrote: 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 ----- "Dale G" wrote: 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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula syntax
"Dale G" wrote:
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 ----- "Dale G" wrote: 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" wrote: "Dale G" wrote: 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 ----- "Dale G" wrote: 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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula syntax
I wrote:
=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 ----- "Joe User" wrote: "Dale G" wrote: 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 ----- "Dale G" wrote: 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" wrote: "Dale G" wrote: 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 ----- "Dale G" wrote: 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. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula syntax
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? "Joe User" wrote: "Dale G" wrote: 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 ----- "Dale G" wrote: 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" wrote: "Dale G" wrote: 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 ----- "Dale G" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help with formula syntax | Excel Discussion (Misc queries) | |||
formula syntax. | Excel Discussion (Misc queries) | |||
Syntax of formula | Excel Worksheet Functions | |||
Formula Syntax | Excel Worksheet Functions | |||
Formula syntax {;;;} | Excel Worksheet Functions |