Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't add 7th IF statement to long formula. | Excel Worksheet Functions | |||
IF statement too long, need another way | Excel Worksheet Functions | |||
very long statement | Excel Discussion (Misc queries) | |||
IF statement too long | Excel Discussion (Misc queries) | |||
Long IF Statement | Excel Discussion (Misc queries) |