Checking for Multiple Values
 

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

Checking for Multiple Values
 

Hi

Try Chip's site:

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

Hope this helps.
Andy.

"guilbj2" wrote in
message ...

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

Checking for Multiple Values
 

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

HIH
--
AP

"guilbj2" a écrit dans
le message de ...

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

Checking for Multiple Values
 

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",E 1<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


--
vencopbrass
------------------------------------------------------------------------
vencopbrass's Profile: http://www.excelforum.com/member.php...o&userid=31868
View this thread: http://www.excelforum.com/showthread...hreadid=516907

Checking for Multiple Values
 

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
------------------------------------------------------------------------
guilbj2's Profile: http://www.excelforum.com/member.php...fo&userid=6043
View this thread: http://www.excelforum.com/showthread...hreadid=516907

Checking for Multiple Values
 

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.

vencopbrass Wrote:
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",E 1<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
------------------------------------------------------------------------
guilbj2's Profile: http://www.excelforum.com/member.php...fo&userid=6043
View this thread: http://www.excelforum.com/showthread...hreadid=516907

Checking for Multiple Values
 

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.


--
guilbj2
------------------------------------------------------------------------
guilbj2's Profile: http://www.excelforum.com/member.php...fo&userid=6043
View this thread: http://www.excelforum.com/showthread...hreadid=516907

Checking for Multiple Values
 

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


--
vencopbrass
------------------------------------------------------------------------
vencopbrass's Profile: http://www.excelforum.com/member.php...o&userid=31868
View this thread: http://www.excelforum.com/showthread...hreadid=516907

Checking for Multiple Values
 

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" wrote in
message ...

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