+ Reply to Thread
Results 1 to 14 of 14

Help ranking tutor groups based on test scores

  1. #1
    Registered User
    Join Date
    06-21-2013
    Location
    Cambridge
    MS-Off Ver
    Excel 2010
    Posts
    7

    Help ranking tutor groups based on test scores

    I'm not really sure how best to describe this. I am trying to allocate scores to different classes on an athletics record sheet. Their times are already written in rank order, but I need the scores to fill in automatically.

    I think I need to use a combination of =rank, =index, =match, taken from raw data elsewhere, and I have used these functions independently, but am getting stuck on how to combine them.

    So looking at the attachment, if 10.6 finish first, 7 points should appear in their total box at the end of the row.

    The formula needs to utilise the rank function in case two scores are the same simultaneously and the points need to be shared. I have used this function before but not to this complicated degree!

    Please help! Thanks

    athletics sheet example.xlsx

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Help ranking tutor groups based on test scores

    Hi and welcome to the forum

    based on your sample data, what would your expected outcome be?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    06-21-2013
    Location
    Cambridge
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Help ranking tutor groups based on test scores

    My expected outcome would be
    10.1 - 4 points
    10.2 - 5
    10.3 - 3
    10.4 - 1
    10.5 - 6
    10.6 - 7
    10.7 - 2

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Help ranking tutor groups based on test scores

    See the attached file.
    Attached Files Attached Files
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Help ranking tutor groups based on test scores

    I dont understand how you arrive at those value

    10.1 gets 4, 10.2 gets 5, but then a higher value gets lower points...10.3 gets 3?

    Can you walk me though exactly how the point get allocated?

  6. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Help ranking tutor groups based on test scores

    @Fdibbins

    I suppose it's an atlete finish file.

    The lowest time (the one who finished first) get's the most points (in this case 7).

  7. #7
    Registered User
    Join Date
    06-21-2013
    Location
    Cambridge
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Help ranking tutor groups based on test scores

    Sorry, I should have explained the figures a bit better. It is a sports day records sheet for athletics.

    the pink box e.g. 10.1 is the class they belong to. It has no real numerical value.
    The blue box is the time/distance recorded for the event.
    The white box is the athletes name.
    The points in the totals box at the end of each row need to be drawn from the time/distance, and therefore the position finished.
    Each form (10.1-10.7) could finish in any position, and there are over 40 events, so it needs to be adaptable. Oeldere, I'm not sure your solution would be able to adapt if the results cchanged.

    Thank you all in advance for your help! Attached is the copy of my entire workbook. on tab "10 boys" there is some mock numbers where you could perhaps try a solution. In cases where scores are tied (I have entered some deliberately) the points would need to be shared e.g. joint 1st = 6.5points each (Average of 7&6=6.5).

    Leeus111

  8. #8
    Registered User
    Join Date
    06-21-2013
    Location
    Cambridge
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Help ranking tutor groups based on test scores

    Sorry, I should have explained the figures a bit better. It is a sports day records sheet for athletics.

    the pink box e.g. 10.1 is the class they belong to. It has no real numerical value.
    The blue box is the time/distance recorded for the event.
    The white box is the athletes name.
    The points in the totals box at the end of each row need to be drawn from the time/distance, and therefore the position finished.
    Each form (10.1-10.7) could finish in any position, and there are over 40 events, so it needs to be adaptable. Oeldere, I'm not sure your solution would be able to adapt if the results cchanged.

    Thank you all in advance for your help! Attached is the copy of my entire workbook. on tab "10 boys" there is some mock numbers where you could perhaps try a solution. In cases where scores are tied (I have entered some deliberately) the points would need to be shared e.g. joint 1st = 6.5points each (Average of 7&6=6.5).

    BLANK athletics Results Sheet SPORTS DAY JULY 2013.xls

    Leeus111

  9. #9
    Registered User
    Join Date
    06-21-2013
    Location
    Cambridge
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Help ranking tutor groups based on test scores

    100M 10 BOYS EXAMPLE DATA.JPG

    I may have worked out a solution to my above problem, however I have done it for one event using the set of data in the picture (the ranked points have been inserted on to my score sheet), but there are close to 80 events. Seeing as this one event took me around about 30 minutes, I don't fancy applying this solution to all the other events.

    Any idea on how to get to the same end point but with a quicker method?

    example formulas on the table are:
    Rank column: =IF('10 BOYS'!$D$5=0,"",RANK('10 BOYS'!$D$5,'10 BOYS'!$D$5:$P$5,1))
    Points column: =AVERAGE(OFFSET($B$70,$D112,0,COUNTIF($D$112:$D$118,$D112)))
    Ranked points column: =OFFSET($B$111,MATCH("10.1",$B$112:$B$118,0),3)

    The only solution I have at the moment is amending all of the formulas by hand to get to the desired event. I am a complete novice regarding macros and VBA, but I know there is a solution utilising this.

    Thanks,

    Leeus111

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Help ranking tutor groups based on test scores

    OK try this, copied down and across...
    =RANK(INDEX($C5:$P5,1,MATCH(TEXT(R$3,"0.0"),$C5:$P5,0)+1),($D5,$F5,$H5,$J5,$L5,$N5,$P5))

  11. #11
    Registered User
    Join Date
    06-21-2013
    Location
    Cambridge
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Help ranking tutor groups based on test scores

    Quote Originally Posted by FDibbins View Post
    OK try this, copied down and across...
    =RANK(INDEX($C5:$P5,1,MATCH(TEXT(R$3,"0.0"),$C5:$P5,0)+1),($D5,$F5,$H5,$J5,$L5,$N5,$P5))
    Thanks, this works for the basic problem I had! There are a few extra things that need doing though, if you would be so kind as to help me further!

    Firstly, for some of the events the rank needs to be reversed e.g. Javelin, the "highest" score is the best, whereas the formula you have given me works for running races where the "lowest" score is the best time. What do I need to change to do this?

    Secondly, I need the points allocated to average in the event of a tied score e.g tied 1st would be average of 7 and 6 points, giving both teams 6.5. I have done this elsewhere using the following formula:

    =IF(C4=0,"",AVERAGE(OFFSET(CALCULATIONS!$B$70,CALCULATIONS!A60,0,COUNTIF(CALCULATIONS!$A$60:$A$66,CALCULATIONS!A60))))

    Can this be incorporated into the formula you have given me?

    Thanks again (all) for your help!

    leeus111

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Help ranking tutor groups based on test scores

    OK Im out of time for now, but I should be able to look at this again in a cpl of hours

  13. #13
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Help ranking tutor groups based on test scores

    First...
    I have modified the formula slightly....
    =RANK(INDEX($C5:$P5,1,MATCH(TEXT(R$3,"0.0"),$C5:$P5,0)+1),($D5,$F5,$H5,$J5,$L5,$N5,$P5),$B5)
    The bolded part will deterimine ascending or descemding, so add a 1 to column B where you want the ranking the other way (you can hide this by makin g the text white on a white background

  14. #14
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Help ranking tutor groups based on test scores

    second....
    The way the rank is working, if there are 2 4th places, there is no 5th place, so it it pretty much doing what you want anyway?

  15. #15
    Registered User
    Join Date
    06-21-2013
    Location
    Cambridge
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Help ranking tutor groups based on test scores

    Great, thanks for the first solution.

    Regarding the second one, the way it is shouldn't affect the overall positions across the whole competition, but it will mean teams are scoring lower total points e.g. at the moment joint first place is scoring 6.0 points, when ideally I want them scoring 6.5. If one team is losing out on 0.5 points in lots of events they may fall a position in the final standings.

    I am trying to create the calculations on the separate tab but encountering a few problems. I have done a very similar thing on the "vertical relay" tab, whereby the scores average out in the event of a tie.

    Thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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