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

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

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