How do find Median of a set of totals and find the relating grade

**whitespaces** · 12-12-2006, 07:02 AM

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.

**Carim** · 12-12-2006, 07:12 AM

Hi,

Have you tried the function ...
=Median()

HTH
Carim

**whitespaces** · 12-12-2006, 07:38 AM

No, the median returns 9. It doesn't seem to work.

**Carim** · 12-12-2006, 08:08 AM

Hi,

Attached is a proposal ...

HTH
Carim

**Json** · 12-12-2006, 11:16 AM

Free.zip

You need a cumulative array to do this I've inserted it as an extra row but it would be far better as a function.

Maybe you can find one within excel; I couldn't, or perhaps you could create a user defined function to do it. I'm affraid that is beyond me at the moment.

Perhaps Carim could help if you have trouble. Carim?