+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1
    Registered User
    Join Date
    12-12-2006
    Posts
    17

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

    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.
    Attached Files Attached Files

  2. #2
    Forum Expert Carim's Avatar
    Join Date
    04-07-2006
    Posts
    4,071
    Hi,

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

    HTH
    Carim

  3. #3
    Registered User
    Join Date
    12-12-2006
    Posts
    17
    No, the median returns 9. It doesn't seem to work.

  4. #4
    Forum Expert Carim's Avatar
    Join Date
    04-07-2006
    Posts
    4,071
    Hi,

    Attached is a proposal ...

    HTH
    Carim
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    11-30-2006
    Location
    UK
    MS-Off Ver
    Microsoft Office XP
    Posts
    61

    Question Answer here but further help needed

    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?

    Jason

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1