Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Feb 2001
    Location
    St. Louis, Missouri, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find top (x) number of results? (Excel 2003)

    I'm keeping track of the standings in a series of 13 skateboard races. I have a worksheet that lists racers' names in rows and their placings and points earned for each race in alternating columns (e.g., for Race A, a certain competitor placed 5th and earned 141 points). I have an additional Autosum column to keep track of the *total* number of points for each racer (this sum is displayed in a separate column).

    My question is, how do I get Excel to calculate and display the sum of each racer's *best nine* races, rather than their total for all 13 races?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Find top (x) number of results? (Excel 2003)

    If you put all the columns with numbers together and make sure they all have numbers you could use an array formula.

    With the setup you have, I think, you will need a custom function. I don't have time to work on one tonite, but I see reading the numbers into an array, sorting it then getting the top values.

    If nobody beats me to it, I will try to get you something later

    Steve

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find top (x) number of results? (Excel 2003)

    The Large() function does what you are asking for. Unfortunately, I can not find a way to get it to work if the values are not in contiguous cells. Therefore, to get it to work, I had to make another copy of the points cells (in AD2:AO132 in the attached workbook).Then I could sum the nine largest with the formula in AC2:AC132. BTW, I could only find 12 races not 13. The attached workbook is compressed because it is too big to upload.
    Legare Coleman

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Find top (x) number of results? (Excel 2003)

    Legare is correct...The large function cannot operate in a non contiguous range. I modified the formula that Legare created sothat it is a little shorter. Try : =SUM(LARGE(AD2:AO2,{1,2,3,4,5,6,7,8,9})). You do not have to press CTRL+SHIFT+ENTER as a normal array function. Simply type it as is and ENTER.
    Regards,
    Rudi

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Find top (x) number of results? (Excel 2003)

    How about the array formula (confirm with ctrl-shift-enter):
    =SUM(IF((MOD(COLUMN(E2:AB2)-5,2)=0)*(E2:AB2>=LARGE(IF(MOD(COLUMN(E2:AB2)-5,2)=0,E2:AB2,""),9)),E2:AB2,0))

    Steve

  6. #6
    New Lounger
    Join Date
    Feb 2001
    Location
    St. Louis, Missouri, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find top (x) number of results? (Excel 2003)

    THANK YOU! :-)

    Steve, Rudi and Legare, each of you provided some useful insights. Given my moderate skill level, I found Legare's solution easiest to understand, but I ended up using Rudi's solution in the final product, because it was shorter and easier to type.

    P.S. Legare, you were correct in only finding 12 races on my original file. I inadvertently posted an older version. The newest file has all 13 races listed. Feel free to take a look at the final product, if you like. (I'm hoping, of course, that I successfully compressed the file before attaching it.)

    Thanks again to all of you.

Posting Permissions

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