Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Ranking Data
Hi, I think this should be easy but I've had a long day......
I've got two columns A) Subject and B) Score. Within column A there are multiple subjects and multiple occurences of each one 5 histories, 4 Art etc etc. I have a student who scored 85% in history, I want to rank that student but only against other history scores. I'm thinking this should be an array formula but whatever I try just doesn't work. Thanks for your help in advance. John |
#2
|
|||
|
|||
John,
=1+SUMPRODUCT(($A$2:$A$100="History")*($B$2:$B$100 B2)) This gives ties the same (highest) rank. HTH, Bernie MS Excel MVP "LJDL" wrote in message ... Hi, I think this should be easy but I've had a long day...... I've got two columns A) Subject and B) Score. Within column A there are multiple subjects and multiple occurences of each one 5 histories, 4 Art etc etc. I have a student who scored 85% in history, I want to rank that student but only against other history scores. I'm thinking this should be an array formula but whatever I try just doesn't work. Thanks for your help in advance. John |
#3
|
|||
|
|||
Better would be
=1+SUMPRODUCT(($A$2:$A$100=A2)*($B$2:$B$100B2)) Then filter your list based on column A to only show one subject at a time. HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... John, =1+SUMPRODUCT(($A$2:$A$100="History")*($B$2:$B$100 B2)) This gives ties the same (highest) rank. HTH, Bernie MS Excel MVP "LJDL" wrote in message ... Hi, I think this should be easy but I've had a long day...... I've got two columns A) Subject and B) Score. Within column A there are multiple subjects and multiple occurences of each one 5 histories, 4 Art etc etc. I have a student who scored 85% in history, I want to rank that student but only against other history scores. I'm thinking this should be an array formula but whatever I try just doesn't work. Thanks for your help in advance. John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting Filtered RC cell information into other worksheets | Excel Discussion (Misc queries) | |||
How do I import formmail data to a custom excel template? | Excel Worksheet Functions | |||
Pull Current Month's Data Out of List - Repost | Excel Discussion (Misc queries) | |||
Line Graph Data Recognition | Charts and Charting in Excel | |||
Running Data Table using an input that triggers DDE linked data | Excel Discussion (Misc queries) |