Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Formula giving incorrect answer...
I have the following formula in cells B2:B163
=INDEX(Schedule!$B$1:$B$1300,SMALL(IF(Schedule!$A$ 1:$A$1300=$A$1,ROW(Schedule!$A$1:$A$1300)),1)) That formula works fine. However, I have the following formula in cells A2:A163 =IF(OR(B2="BAL",B2="BOS",B2="NYY",B2="TB",B2="TOR" ,B2="CWS",B2="CLE",B2="DET",B2="KC",B2="MIN",B2="L AA",B2="OAK",B2="SEA",B2="TEX"),"AL","NL") For some reason I get NL in all of the column A cells even if the cells in B are BAL, BOS, NYY, etc. If I just type in BAL (and overwrite the formula) in cell B2, cell A2 will correctly give me AL. Any ideas why it is doing this? Thanks |
#2
|
|||
|
|||
Hi
Probably you had trailing blanks in column B, i.e. the formula p.e. in B2 doesn't return "BAL", but " BAL" or "BAL " or "BAL ", ... When this is the case, remove trailing blanks from Shedule!B1:B1300 (select the range, ReplaceAll " " with "") You also can use TRIM in your IF() contitions. Here is somewhat simpler formula, where you don't have to use the TRIM for every condition separately: =IF(ISNA(MATCH(TRIM(B2),{"BAL";"BOS";"NYY";"TB";"T OR";"CWS";"CLE";"DET";"KC" ;"MIN";"LAA";"OAK";"SEA";"TEX"},0)),"NL","AL") -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets "Jambruins" wrote in message ... I have the following formula in cells B2:B163 =INDEX(Schedule!$B$1:$B$1300,SMALL(IF(Schedule!$A$ 1:$A$1300=$A$1,ROW(Schedul e!$A$1:$A$1300)),1)) That formula works fine. However, I have the following formula in cells A2:A163 =IF(OR(B2="BAL",B2="BOS",B2="NYY",B2="TB",B2="TOR" ,B2="CWS",B2="CLE",B2="DET ",B2="KC",B2="MIN",B2="LAA",B2="OAK",B2="SEA",B2=" TEX"),"AL","NL") For some reason I get NL in all of the column A cells even if the cells in B are BAL, BOS, NYY, etc. If I just type in BAL (and overwrite the formula) in cell B2, cell A2 will correctly give me AL. Any ideas why it is doing this? Thanks |
#3
|
|||
|
|||
On Fri, 25 Feb 2005 05:23:08 -0800, Jambruins
wrote: I have the following formula in cells B2:B163 =INDEX(Schedule!$B$1:$B$1300,SMALL(IF(Schedule!$A $1:$A$1300=$A$1,ROW(Schedule!$A$1:$A$1300)),1)) That formula works fine. However, I have the following formula in cells A2:A163 =IF(OR(B2="BAL",B2="BOS",B2="NYY",B2="TB",B2="TOR ",B2="CWS",B2="CLE",B2="DET",B2="KC",B2="MIN",B2=" LAA",B2="OAK",B2="SEA",B2="TEX"),"AL","NL") For some reason I get NL in all of the column A cells even if the cells in B are BAL, BOS, NYY, etc. If I just type in BAL (and overwrite the formula) in cell B2, cell A2 will correctly give me AL. Any ideas why it is doing this? Thanks Most likely, the formula in B2 is not returning "exactly" "BAL". Perhaps there is an extra space or some other non-printing character in Schedule! --ron |
#4
|
|||
|
|||
Thank you all for the help. Arvi, when I pasted your trim formula in it
worked perfectly. Thanks again. "Arvi Laanemets" wrote: Hi Probably you had trailing blanks in column B, i.e. the formula p.e. in B2 doesn't return "BAL", but " BAL" or "BAL " or "BAL ", ... When this is the case, remove trailing blanks from Shedule!B1:B1300 (select the range, ReplaceAll " " with "") You also can use TRIM in your IF() contitions. Here is somewhat simpler formula, where you don't have to use the TRIM for every condition separately: =IF(ISNA(MATCH(TRIM(B2),{"BAL";"BOS";"NYY";"TB";"T OR";"CWS";"CLE";"DET";"KC" ;"MIN";"LAA";"OAK";"SEA";"TEX"},0)),"NL","AL") -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets "Jambruins" wrote in message ... I have the following formula in cells B2:B163 =INDEX(Schedule!$B$1:$B$1300,SMALL(IF(Schedule!$A$ 1:$A$1300=$A$1,ROW(Schedul e!$A$1:$A$1300)),1)) That formula works fine. However, I have the following formula in cells A2:A163 =IF(OR(B2="BAL",B2="BOS",B2="NYY",B2="TB",B2="TOR" ,B2="CWS",B2="CLE",B2="DET ",B2="KC",B2="MIN",B2="LAA",B2="OAK",B2="SEA",B2=" TEX"),"AL","NL") For some reason I get NL in all of the column A cells even if the cells in B are BAL, BOS, NYY, etc. If I just type in BAL (and overwrite the formula) in cell B2, cell A2 will correctly give me AL. Any ideas why it is doing this? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I prevent incorrect formula results appearing in cell? | Excel Worksheet Functions | |||
Display answer only in another cell of one containing a formula | Excel Discussion (Misc queries) | |||
Sum Function sometimes displays incorrect answer | Excel Worksheet Functions | |||
display the answer for a formula from another page? | New Users to Excel | |||
Formula window displays correct answer while cell displays incorre | Excel Worksheet Functions |