Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Sum top 4 scores in list of 7

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Dublin, Ireland
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi! When I looked at this, I thought I could do it quickly but found that's it's more tricky.

    I have a list of seven scores recorded in columns B through H against each of say 50 people. In column I, I want to sum the top 4 scores out of the seven scores recorded by each person.

    Sorting the fields horizontally is not an option because each score must remain under the event in which it was recorded. Transposing the data for manipulation is also a no go as there are other formulae at work on this worksheet. I think the answer must lie in a formula (or combined formulae). Lastly, I need the solution to be flexible, say I want to take the top 5 scores from 8 events for example.

    If you have any ideas, I'd appreciate your comments, thanks!

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi


    In any cell put:


    =Large($B$2:$H$100,A1)

    Copy this down 4 rows. Now in A1:A4 put the numbers 1 to 4. In the cell below the LARGE function put your SUM formula.


    _________________
    Kind Regards
    Dave Hawley
    OzGrid Business Applications
    Microsoft Excel/VBA Training


    [ This Message was edited by: Dave Hawley on 2002-04-09 04:47 ]

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    Dublin, Ireland
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Classy Dave - many thanks!

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,424
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default



    =SUM(LARGE(B3:H3,{1,2,3,4}))

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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