| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup + Max
I've created a spreadsheet to tabulate scores for a band competition.
I need formulas to extract the high score in different classes and categories. Class Band "Drum Major" "Color Guard" "Dance Line" Majorettes 1A Abbeville 85 86 1A New Brockton 85 73 85 86 1A Dale County 82 90 86 2A Early County 90 93 78 2A W.S. Neal 92 93 90 2A Russell County 97 88 3A Geneva 85 81 88 3A Valley 87 97 88 Best in Class 1A 2A 3A Drum Major Rehobeth Early County Geneva Color Guard Freeport W.S. Neal Geneva Dance Line Saint James W.S. Neal Majorettes Slocomb W.S. Neal Andalusia Ex. I need the formula to extract the high score for Drum Major in Class 1A. How do I do this? Thanks |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup + Max
I need the formula to extract the high score for Drum Major
in Class 1A. A2:A9 = class C1:E1 = column headers: Drum Major, Color Guard, Dance Line C2:E9 = scores Try this array formula** : =MAX(IF(A2:A9="1A",C2:C9)) Or, to make it flexible where you can choose the category, enter the category you want in a cell: A25 = Drum Major Also an array formula** : =MAX(IF(A2:A9="1A",INDEX(C2:E9,,MATCH(A25,C1:E1,0) ))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP wrote in message ... I've created a spreadsheet to tabulate scores for a band competition. I need formulas to extract the high score in different classes and categories. Class Band "Drum Major" "Color Guard" "Dance Line" Majorettes 1A Abbeville 85 86 1A New Brockton 85 73 85 86 1A Dale County 82 90 86 2A Early County 90 93 78 2A W.S. Neal 92 93 90 2A Russell County 97 88 3A Geneva 85 81 88 3A Valley 87 97 88 Best in Class 1A 2A 3A Drum Major Rehobeth Early County Geneva Color Guard Freeport W.S. Neal Geneva Dance Line Saint James W.S. Neal Majorettes Slocomb W.S. Neal Andalusia Ex. I need the formula to extract the high score for Drum Major in Class 1A. How do I do this? Thanks |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup + Max
On Oct 20, 3:29*pm, "T. Valko" wrote:
I need the formula to extract the high score for Drum Major in Class 1A. A2:A9 = class C1:E1 = column headers: Drum Major, Color Guard, Dance Line C2:E9 = scores Try this array formula** : =MAX(IF(A2:A9="1A",C2:C9)) Or, to make it flexible where you can choose the category, enter the category you want in a cell: A25 = Drum Major Also an array formula** : =MAX(IF(A2:A9="1A",INDEX(C2:E9,,MATCH(A25,C1:E1,0) ))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP wrote in message ... I've created a spreadsheet to tabulate scores for a band competition. I need formulas to extract the high score in different classes and categories. Class Band * * * * * "Drum Major" "Color Guard" "Dance Line" Majorettes 1A Abbeville * * * * * * * * 85 * * * * *86 1A New Brockton 85 * * * * *73 85 * * * * * * * * * * 86 1A Dale County 82 * * * * *90 * * * * 86 2A Early County 90 * * * * *93 * * * * 78 2A W.S. Neal * * * * * * * * 92 * * * * *93 * * * * 90 2A Russell County 97 * * * * *88 3A Geneva * * * * * * * * 85 * * * * *81 * * * * 88 3A Valley * * * * * * * * 87 * * * * *97 * * * * 88 Best in Class 1A 2A 3A Drum Major * * * * * Rehobeth * * * * * * * * * * * *Early County Geneva Color Guard * * * * * Freeport * * * * *W.S. Neal Geneva Dance Line * * * *Saint James * * * * W.S. Neal Majorettes * * * * * * Slocomb * * * * * * * * * * * * * W.S. Neal Andalusia Ex. I need the formula to extract the high score for Drum Major in Class 1A. How do I do this? Thanks- Hide quoted text - - Show quoted text - How do I get it to output the name of the school. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup + Max
How do I get it to output the name of the school.
What if there are ties like in your sample? 1A Abbeville 85 1A New Brockton 85 1A Dale County 82 Do you want both schools? -- Biff Microsoft Excel MVP wrote in message ... On Oct 20, 3:29 pm, "T. Valko" wrote: I need the formula to extract the high score for Drum Major in Class 1A. A2:A9 = class C1:E1 = column headers: Drum Major, Color Guard, Dance Line C2:E9 = scores Try this array formula** : =MAX(IF(A2:A9="1A",C2:C9)) Or, to make it flexible where you can choose the category, enter the category you want in a cell: A25 = Drum Major Also an array formula** : =MAX(IF(A2:A9="1A",INDEX(C2:E9,,MATCH(A25,C1:E1,0) ))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP wrote in message ... I've created a spreadsheet to tabulate scores for a band competition. I need formulas to extract the high score in different classes and categories. Class Band "Drum Major" "Color Guard" "Dance Line" Majorettes 1A Abbeville 85 86 1A New Brockton 85 73 85 86 1A Dale County 82 90 86 2A Early County 90 93 78 2A W.S. Neal 92 93 90 2A Russell County 97 88 3A Geneva 85 81 88 3A Valley 87 97 88 Best in Class 1A 2A 3A Drum Major Rehobeth Early County Geneva Color Guard Freeport W.S. Neal Geneva Dance Line Saint James W.S. Neal Majorettes Slocomb W.S. Neal Andalusia Ex. I need the formula to extract the high score for Drum Major in Class 1A. How do I do this? Thanks- Hide quoted text - - Show quoted text - How do I get it to output the name of the school. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup + Max
On Oct 20, 3:53*pm, "T. Valko" wrote:
How do I get it to output the name of the school. What if there are ties like in your sample? 1A Abbeville 85 1A New Brockton 85 1A Dale County 82 Do you want both schools? -- Biff Microsoft Excel MVP wrote in message ... On Oct 20, 3:29 pm, "T. Valko" wrote: I need the formula to extract the high score for Drum Major in Class 1A. A2:A9 = class C1:E1 = column headers: Drum Major, Color Guard, Dance Line C2:E9 = scores Try this array formula** : =MAX(IF(A2:A9="1A",C2:C9)) Or, to make it flexible where you can choose the category, enter the category you want in a cell: A25 = Drum Major Also an array formula** : =MAX(IF(A2:A9="1A",INDEX(C2:E9,,MATCH(A25,C1:E1,0) ))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP wrote in message .... I've created a spreadsheet to tabulate scores for a band competition. I need formulas to extract the high score in different classes and categories. Class Band "Drum Major" "Color Guard" "Dance Line" Majorettes 1A Abbeville 85 86 1A New Brockton 85 73 85 86 1A Dale County 82 90 86 2A Early County 90 93 78 2A W.S. Neal 92 93 90 2A Russell County 97 88 3A Geneva 85 81 88 3A Valley 87 97 88 Best in Class 1A 2A 3A Drum Major Rehobeth Early County Geneva Color Guard Freeport W.S. Neal Geneva Dance Line Saint James W.S. Neal Majorettes Slocomb W.S. Neal Andalusia Ex. I need the formula to extract the high score for Drum Major in Class 1A. How do I do this? Thanks- Hide quoted text - - Show quoted text - How do I get it to output the name of the school.- Hide quoted text - - Show quoted text - Yesn I'll need both schools. Thanks |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup + Max
Ok, it's complicated!
Class in column A School in colimn B Scores in column C Assume you enter the array formula** in A10. =IF(ROWS(A$10:A10)<=SUM((A$2:A$7="1A")*(C$2:C$7=MA X(IF(A$2:A$7="1A",C$2:C$7)))),INDEX(B$2:B$7,SMALL( IF((A$2:A$7="1A")*(C$2:C$7=MAX(IF(A$2:A$7="1A",C$2 :C$7))),ROW(B$2:B$7)),ROWS(A$10:A10))-MIN(ROW(B$2:B$7))+1),"") Copy down until you get blanks ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP wrote in message ... On Oct 20, 3:53 pm, "T. Valko" wrote: How do I get it to output the name of the school. What if there are ties like in your sample? 1A Abbeville 85 1A New Brockton 85 1A Dale County 82 Do you want both schools? -- Biff Microsoft Excel MVP wrote in message ... On Oct 20, 3:29 pm, "T. Valko" wrote: I need the formula to extract the high score for Drum Major in Class 1A. A2:A9 = class C1:E1 = column headers: Drum Major, Color Guard, Dance Line C2:E9 = scores Try this array formula** : =MAX(IF(A2:A9="1A",C2:C9)) Or, to make it flexible where you can choose the category, enter the category you want in a cell: A25 = Drum Major Also an array formula** : =MAX(IF(A2:A9="1A",INDEX(C2:E9,,MATCH(A25,C1:E1,0) ))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP wrote in message ... I've created a spreadsheet to tabulate scores for a band competition. I need formulas to extract the high score in different classes and categories. Class Band "Drum Major" "Color Guard" "Dance Line" Majorettes 1A Abbeville 85 86 1A New Brockton 85 73 85 86 1A Dale County 82 90 86 2A Early County 90 93 78 2A W.S. Neal 92 93 90 2A Russell County 97 88 3A Geneva 85 81 88 3A Valley 87 97 88 Best in Class 1A 2A 3A Drum Major Rehobeth Early County Geneva Color Guard Freeport W.S. Neal Geneva Dance Line Saint James W.S. Neal Majorettes Slocomb W.S. Neal Andalusia Ex. I need the formula to extract the high score for Drum Major in Class 1A. How do I do this? Thanks- Hide quoted text - - Show quoted text - How do I get it to output the name of the school.- Hide quoted text - - Show quoted text - Yesn I'll need both schools. Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Matrix lookup/mulitple criteria lookup | Excel Discussion (Misc queries) | |||
| Get Cell Address From Lookup (Alternative to Lookup) | Excel Worksheet Functions | |||
| Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
| Sumproduct - Condition based on lookup of a Lookup | Excel Discussion (Misc queries) | |||
| Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) | |||
Hybrid Mode
