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

On Fri, 25 Feb 2005 05:23:08 -0800, Jambruins
<Jambruins@discussions.microsoft.com> 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

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";"TOR";"CWS";"CLE";"DET";"KC"
;"MIN";"LAA";"OAK";"SEA";"TEX"},0)),"NL","AL")

--
When sending mail, use address arvil<at>tarkon.ee
Arvi Laanemets


"Jambruins" <Jambruins@discussions.microsoft.com> wrote in message
news:AEC78AD0-907A-488F-A9EC-9BD2DCC0FC0F@microsoft.com...
> 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
>

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";"TOR";"CWS";"CLE";"DET";"KC"
> ;"MIN";"LAA";"OAK";"SEA";"TEX"},0)),"NL","AL")
>
> --
> When sending mail, use address arvil<at>tarkon.ee
> Arvi Laanemets
>
>
> "Jambruins" <Jambruins@discussions.microsoft.com> wrote in message
> news:AEC78AD0-907A-488F-A9EC-9BD2DCC0FC0F@microsoft.com...
> > 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
> >
>
>
>