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

1. 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. 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. 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.

4. 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.

5. 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. 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
•