Checking for Multiple Values

[guilbj2]

Man... this one has me stumped. I'm getting a little further into =IF that I have previously and I'm hoping someone can point me in the right direction. Agents in our business have the following goals based on how long they've been with the company (lower number = more seniority, more difficult to hit). I

340 = Category A
350 = Category B
350 = Category C
300 = Category D
280 = Category E
250 = Category F
230 = Category G

I'm going to have one column (Lets say D) with all of their categories. In a second column (E) I'll have their results. I'd like to craft an =IF that will check to see the category, and then see if they've come in under their goal... something like these ones, but combined into one formula rather than 7 individual ones:

=IF(AND(D1="A",E1<340), "WIN", "LOSS")
=IF(AND(D1="B",E1<350), "WIN", "LOSS")
=IF(AND(D1="C",E1<350), "WIN", "LOSS")
=IF(AND(D1="D",E1<300), "WIN", "LOSS")
=IF(AND(D1="E",E1<280), "WIN", "LOSS")
=IF(AND(D1="F",E1<250), "WIN", "LOSS")
=IF(AND(D1="G",E1<230), "WIN", "LOSS")

These formulas work great for one category, but I want to create a single formula that basically checks which category is listed in column D and then applies the proper criteria for the goal.

I've always considered myself a pretty educated Excel user, but I am in awe of the level of ability many of you have. Hopefully someone can come to my rescue.

Hi

Try Chip's site:

http://www.cpearson.com/excel/excelF.htm#Grades

Hope this helps.
Andy.

"guilbj2" <[email protected]> wrote in
message news:[email protected]...
>
> Man... this one has me stumped. I'm getting a little further into =IF
> that I have previously and I'm hoping someone can point me in the right
> direction. Agents in our business have the following goals based on how
> long they've been with the company (lower number = more seniority, more
> difficult to hit). I
>
> 340 = Category A
> 350 = Category B
> 350 = Category C
> 300 = Category D
> 280 = Category E
> 250 = Category F
> 230 = Category G
>
> I'm going to have one column (Lets say D) with all of their categories.
> In a second column (E) I'll have their results. I'd like to craft an
> =IF that will check to see the category, and then see if they've come
> in under their goal... something like these ones, but combined into
> one formula rather than 7 individual ones:
>
> =IF(AND(D1="A",E1<340), "WIN", "LOSS")
> =IF(AND(D1="B",E1<350), "WIN", "LOSS")
> =IF(AND(D1="C",E1<350), "WIN", "LOSS")
> =IF(AND(D1="D",E1<300), "WIN", "LOSS")
> =IF(AND(D1="E",E1<280), "WIN", "LOSS")
> =IF(AND(D1="F",E1<250), "WIN", "LOSS")
> =IF(AND(D1="G",E1<230), "WIN", "LOSS")
>
> These formulas work great for one category, but I want to create a
> single formula that basically checks which category is listed in column
> D and then applies the proper criteria for the goal.
>
> I've always considered myself a pretty educated Excel user, but I am in
> awe of the level of ability many of you have. Hopefully someone can
> come to my rescue.
>
>
> --
> guilbj2
> ------------------------------------------------------------------------
> guilbj2's Profile:
> http://www.excelforum.com/member.php...fo&userid=6043
> View this thread: http://www.excelforum.com/showthread...hreadid=516907
>

[Ardus Petus]

Here is an example:
http://cjoint.com/?cBsgacBIfw

HIH
--
AP

"guilbj2" <[email protected]> a écrit dans
le message de news:[email protected]...
>
> Man... this one has me stumped. I'm getting a little further into =IF
> that I have previously and I'm hoping someone can point me in the right
> direction. Agents in our business have the following goals based on how
> long they've been with the company (lower number = more seniority, more
> difficult to hit). I
>
> 340 = Category A
> 350 = Category B
> 350 = Category C
> 300 = Category D
> 280 = Category E
> 250 = Category F
> 230 = Category G
>
> I'm going to have one column (Lets say D) with all of their categories.
> In a second column (E) I'll have their results. I'd like to craft an
> =IF that will check to see the category, and then see if they've come
> in under their goal... something like these ones, but combined into
> one formula rather than 7 individual ones:
>
> =IF(AND(D1="A",E1<340), "WIN", "LOSS")
> =IF(AND(D1="B",E1<350), "WIN", "LOSS")
> =IF(AND(D1="C",E1<350), "WIN", "LOSS")
> =IF(AND(D1="D",E1<300), "WIN", "LOSS")
> =IF(AND(D1="E",E1<280), "WIN", "LOSS")
> =IF(AND(D1="F",E1<250), "WIN", "LOSS")
> =IF(AND(D1="G",E1<230), "WIN", "LOSS")
>
> These formulas work great for one category, but I want to create a
> single formula that basically checks which category is listed in column
> D and then applies the proper criteria for the goal.
>
> I've always considered myself a pretty educated Excel user, but I am in
> awe of the level of ability many of you have. Hopefully someone can
> come to my rescue.
>
>
> --
> guilbj2
> ------------------------------------------------------------------------
> guilbj2's Profile:
http://www.excelforum.com/member.php...fo&userid=6043
> View this thread: http://www.excelforum.com/showthread...hreadid=516907
>

[vencopbrass]

Here is another way to do it.
Type or paste this into F1 or wherever you want the win/loss to show up.
Then drag it down to fill in the other rows.
=IF(AND(D1="A",E1<340),"WIN",IF(AND(D1="B",E1<350),"WIN",IF(AND(D1="C",E1<350),"WIN",IF(AND(D1="D",E1<300),"WIN",IF(AND(D1="E",E1<280), "WIN",IF(AND(D1="F",E1<250), "WIN",IF(AND(D1="G",E1<230),"WIN","LOSS")))))))

Lisa

[guilbj2]

I'm afraid that's not really what I'm looking for. I've already got the numbers associated with the goals, it's getting the =IF logic gate to check which category they're in and THEN if they passed or not that I need.

[guilbj2]

Hi Lisa,

This is exactly what I'm looking for but it doesn't quite work. I've tried a test and entered value for D1 as "A" and E1 as 320, but I'm still getting "LOSS" when it should be "WIN".

Thanks a ton for your help, hopefully you'll indulge me a little more.

Here is another way to do it.
Type or paste this into F1 or wherever you want the win/loss to show up.
Then drag it down to fill in the other rows.
=IF(AND(D1="A",E1<340),"WIN",IF(AND(D1="B",E1<350),"WIN",IF(AND(D1="C",E1<350),"WIN",IF(AND(D1="D",E1<300),"WIN",IF(AND(D1="E",E1<280), "WIN",IF(AND(D1="F",E1<250), "WIN",IF(AND(D1="G",E1<230),"WIN","LOSS")))))))

Lisa

[guilbj2]

Sorry Lisa, I just found the error. I had the values already entered into the cells when I entered your formula. When I re-enter them, everything works perfectly. Thanks again for your help.

[vencopbrass]

No problem, I'm glad I could be of some help. Also I have a question posted that I am unable to get help on (so far). Maybe you can assist. Its called limited fraction cell format....I think.
Lisa

[Ron P]

I would try to solve your problem by combining an "if" statement with a
"vlookup".

I created a table M1:N7 like the following

a 340
b 350
c 350
d 300
e 280
f 250
g 230


Starting in A1 I had the following:
A B C D
1 Name Category Score W/L
2
=IF(VLOOKUP(B2,$M$1:$N$7,2,FALSE)>C2,"win","loss")
3

Copy the formula down as far as required.

Hope this helps

Ron

"guilbj2" <[email protected]> wrote in
message news:[email protected]...
>
> Man... this one has me stumped. I'm getting a little further into =IF
> that I have previously and I'm hoping someone can point me in the right
> direction. Agents in our business have the following goals based on how
> long they've been with the company (lower number = more seniority, more
> difficult to hit). I
>
> 340 = Category A
> 350 = Category B
> 350 = Category C
> 300 = Category D
> 280 = Category E
> 250 = Category F
> 230 = Category G
>
> I'm going to have one column (Lets say D) with all of their categories.
> In a second column (E) I'll have their results. I'd like to craft an
> =IF that will check to see the category, and then see if they've come
> in under their goal... something like these ones, but combined into
> one formula rather than 7 individual ones:
>
> =IF(AND(D1="A",E1<340), "WIN", "LOSS")
> =IF(AND(D1="B",E1<350), "WIN", "LOSS")
> =IF(AND(D1="C",E1<350), "WIN", "LOSS")
> =IF(AND(D1="D",E1<300), "WIN", "LOSS")
> =IF(AND(D1="E",E1<280), "WIN", "LOSS")
> =IF(AND(D1="F",E1<250), "WIN", "LOSS")
> =IF(AND(D1="G",E1<230), "WIN", "LOSS")
>
> These formulas work great for one category, but I want to create a
> single formula that basically checks which category is listed in column
> D and then applies the proper criteria for the goal.
>
> I've always considered myself a pretty educated Excel user, but I am in
> awe of the level of ability many of you have. Hopefully someone can
> come to my rescue.
>
>
> --
> guilbj2
> ------------------------------------------------------------------------
> guilbj2's Profile:
> http://www.excelforum.com/member.php...fo&userid=6043
> View this thread: http://www.excelforum.com/showthread...hreadid=516907
>