Hello there, can anyone help me I have a list of data such like below:
Subject A* A B C D E F G U
3120 0 0 2 3 9 12 15 30 37
I want to find out the median grade, at the moment I find the mode by using:
=IF($G57=0,"",INDEX($H$1:$AD$1,0,MATCH(MAX(H57:AD57),H57:AD57,0)))
(It returns a predicted grade)
So I get 'U', however this is not really correct, I want to get the median so I want it to count up the number of students e.g. 108 and then work out which grade the 54th pupil lies, so in this example it will be G. I have attached an extract of data to show the above a bit better.
By the way, I would be able to write a vba function to do this but I need to stick to Worksheet formulas so users don't have to enable macros each time they open workbook.
Many thanks for any help.
Bookmarks