Results 1 to 6 of 6

20040907, 21:24 #1
 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?

20040907, 22:58 #2
 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

20040908, 00:50 #3
 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

20040908, 06:35 #4
 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

20040908, 09:38 #5
 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 ctrlshiftenter):
=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

20040908, 14:49 #6
 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.