Checking for Multiple Values

[guilbj2]

Man... this one has me stumped. I'm getting a little further into =I
that I have previously and I'm hoping someone can point me in the righ
direction. Agents in our business have the following goals based on ho
long they've been with the company (lower number = more seniority, mor
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 a
=IF that will check to see the category, and then see if they've com
in under their goal... something like these ones, but combined int
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
single formula that basically checks which category is listed in colum
D and then applies the proper criteria for the goal.

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

 

[Guest]

Hi

Try Chip's site:

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

Hope this helps.
Andy.

 

[Ardus Petus]

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

HIH

 

[vencopbrass]

Here is another way to do it.
Type or paste this into F1 or wherever you want the win/loss to sho
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")))))))

Lis

 

[guilbj2]

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

 

[guilbj2]

Hi Lisa,

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

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

 

[guilbj2]

Sorry Lisa, I just found the error. I had the values already entere
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 questio
posted that I am unable to get help on (so far). Maybe you can assist
Its called limited fraction cell format....I think.
Lis

 

[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