Long If Statement
 

I currently have a long If statement to calculate a person's performance
based on their scores. I believe the if statement is too long because Excel
is not liking the formula after I added two more parts to it. If someone has
any idea on how to make this work, I would be more than grateful.


=IF(AND(C3=114,D3=92),"Champion",if(and(c3=114, d3=75),"Oriented",if(and(c3107,
d3-77),"Oriented",if(and(c3=100,d3=79),"Oriented",I f(and(c3=86,d3=80),"Oriented",if(and(c3=85,d3=8 6),"Oriented",if(and(c3=84,d3=87),"Oriented",if(a nd(c3=82,d3=89),
"Oriented",if(and(c3=80,d3=91),"Oriented","Perfo rmance")))))))))

Columns C and D basically just have diffent scores for their performances.

Thanks

Long If Statement
 

Logically, what differentiates a Champion from Oriented from Performance?
Those seem to be the only 3 classifications. You could create an aggregate
score based on the two performance scores and then use this to VLOOKUP a
classficiation
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"Michael" wrote:

I currently have a long If statement to calculate a person's performance
based on their scores. I believe the if statement is too long because Excel
is not liking the formula after I added two more parts to it. If someone has
any idea on how to make this work, I would be more than grateful.


=IF(AND(C3=114,D3=92),"Champion",if(and(c3=114, d3=75),"Oriented",if(and(c3107,
d3-77),"Oriented",if(and(c3=100,d3=79),"Oriented",I f(and(c3=86,d3=80),"Oriented",if(and(c3=85,d3=8 6),"Oriented",if(and(c3=84,d3=87),"Oriented",if(a nd(c3=82,d3=89),
"Oriented",if(and(c3=80,d3=91),"Oriented","Perfo rmance")))))))))

Columns C and D basically just have diffent scores for their performances.

Thanks

Long If Statement
 

In pre-2007 version of Excel you can nest to 7 levels
I have a solution to your problem using MATCH and INDEX
It would take too long to give description
Email me (take TRUENORTH out) and I will send file
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Michael" wrote in message
...
I currently have a long If statement to calculate a person's performance
based on their scores. I believe the if statement is too long because
Excel
is not liking the formula after I added two more parts to it. If someone
has
any idea on how to make this work, I would be more than grateful.


=IF(AND(C3=114,D3=92),"Champion",if(and(c3=114, d3=75),"Oriented",if(and(c3107,
d3-77),"Oriented",if(and(c3=100,d3=79),"Oriented",I f(and(c3=86,d3=80),"Oriented",if(and(c3=85,d3=8 6),"Oriented",if(and(c3=84,d3=87),"Oriented",if(a nd(c3=82,d3=89),
"Oriented",if(and(c3=80,d3=91),"Oriented","Perfo rmance")))))))))

Columns C and D basically just have diffent scores for their performances.

Thanks

Long If Statement
 

We have a grid based on the two metrics. We can only look at the two metrics
when trying to find their overall performance. We have a grid so is there a
way to use vlookup to find the spot on the grid that it corresponds to? The
grid is too large to paste here.

Thanks

"M Kan" wrote:

Logically, what differentiates a Champion from Oriented from Performance?
Those seem to be the only 3 classifications. You could create an aggregate
score based on the two performance scores and then use this to VLOOKUP a
classficiation
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"Michael" wrote:

I currently have a long If statement to calculate a person's performance
based on their scores. I believe the if statement is too long because Excel
is not liking the formula after I added two more parts to it. If someone has
any idea on how to make this work, I would be more than grateful.


=IF(AND(C3=114,D3=92),"Champion",if(and(c3=114, d3=75),"Oriented",if(and(c3107,
d3-77),"Oriented",if(and(c3=100,d3=79),"Oriented",I f(and(c3=86,d3=80),"Oriented",if(and(c3=85,d3=8 6),"Oriented",if(and(c3=84,d3=87),"Oriented",if(a nd(c3=82,d3=89),
"Oriented",if(and(c3=80,d3=91),"Oriented","Perfo rmance")))))))))

Columns C and D basically just have diffent scores for their performances.

Thanks

Long If Statement
 

On Mon, 21 Jul 2008 08:46:02 -0700, Michael
wrote:

We have a grid based on the two metrics. We can only look at the two metrics
when trying to find their overall performance. We have a grid so is there a
way to use vlookup to find the spot on the grid that it corresponds to? The
grid is too large to paste here.

Thanks

"M Kan" wrote:

Logically, what differentiates a Champion from Oriented from Performance?
Those seem to be the only 3 classifications. You could create an aggregate
score based on the two performance scores and then use this to VLOOKUP a
classficiation
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"Michael" wrote:

I currently have a long If statement to calculate a person's performance
based on their scores. I believe the if statement is too long because Excel
is not liking the formula after I added two more parts to it. If someone has
any idea on how to make this work, I would be more than grateful.


=IF(AND(C3=114,D3=92),"Champion",if(and(c3=114, d3=75),"Oriented",if(and(c3107,
d3-77),"Oriented",if(and(c3=100,d3=79),"Oriented",I f(and(c3=86,d3=80),"Oriented",if(and(c3=85,d3=8 6),"Oriented",if(and(c3=84,d3=87),"Oriented",if(a nd(c3=82,d3=89),
"Oriented",if(and(c3=80,d3=91),"Oriented","Perfo rmance")))))))))

Columns C and D basically just have diffent scores for their performances.

Thanks



0 80 81 82 83 84 85 86
87 88 89 90 91 92 93 94 95
96 97 98 99 100 101 102 103 104
105 106 107 108 109 110 111 112 113
114
0 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1
1
75 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1
2
76 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1
2
77 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1
1 1 2 2 2 2 2 2 2
2
78 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1
1 1 2 2 2 2 2 2 2
2
79 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1
1 1 1 1 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2
80 1 1 1 1 1 1 1 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2
81 1 1 1 1 1 1 1 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2
82 1 1 1 1 1 1 1 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2
83 1 1 1 1 1 1 1 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2
84 1 1 1 1 1 1 1 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2
85 1 1 1 1 1 1 1 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2
86 1 1 1 1 1 1 2 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2
87 1 1 1 1 1 2 2 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2
88 1 1 1 1 1 2 2 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2
89 1 1 1 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2
90 1 1 1 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2
91 1 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2
92 1 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
3


If you put the above table in cells A1:AK20 of Sheet2 then you can try
the following formula to find the score.

=CHOOSE(VLOOKUP(D3,Sheet2!A1:AK20,MATCH(C3,Sheet2! A1:AK1)),"Performance","Oriented","Champion")

Hope this helps / Lars-Åke

Long If Statement
 

Michael wrote...
....
=IF(AND(C3=114,D3=92),"Champion",
if(and(c3=114,d3=75),"Oriented",
if(and(c3107,d3-77),"Oriented",
if(and(c3=100,d3=79),"Oriented",
If(and(c3=86,d3=80),"Oriented",
if(and(c3=85,d3=86),"Oriented",
if(and(c3=84,d3=87),"Oriented",
if(and(c3=82,d3=89),"Oriented",
if(and(c3=80,d3=91),"Oriented",
"Performance")))))))))
....

So if I'm reading this right, if both col C = 114 and col D = 92,
the result should be Champion, but there's a trade-off between cols C
and D to determine whether an individual is Oriented or Performance.
That could be handled using nested lookups, e.g.,

=IF(AND(C3=114,D3=92),"Champion",
IF(D3=LOOKUP(C3,{80;82;84;85;86;100;107;114},
{91;89;87;86;80;79;77;75}),"Oriented","Performance "))

Safer and more flexible to put these ranges into a 2-column table.