Long If Statement

[Michael]

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(c3>107,
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")))))))))

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

Thanks

 

[M Kan]

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

 

[Bernard Liengme]

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

 

[Michael]

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

 

[Lars-Åke Aspelin]

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

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

 

[Harlan Grove]

=IF(AND(C3>=114,D3>=92),"Champion",
if(and(c3>=114,d3>=75),"Oriented",
if(and(c3>107,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.