Thanks:  0
Likes:  0

# Thread: Summing top 5 of 7 values

1. We have a non profit club that races 1/10th scale racing cars. We have 7 races in each series and only count the top 5 point results. We post the results using an Excel spreadsheet with names down one collumn and points accumulated down corresponding collumns. You can check it out at:
http://www.igs.net/~rbeaudet/NCMA/rc...er20012002.htm

The question:
How can I sum the top 5 values across a row of 7 numbers?

2. Well, two options.

=SUM(LARGE(\$A\$1:\$G\$1,{1,2,3,4,5}))

or a slightly different one

=SUM(\$A\$1:\$G\$1)-SUM(SMALL(A1:G1,{1,2}))

3. Juan Pablo G.:
Thanks for the quick reply. The formula works very well.

I now have another condition which affects the results. The points standings have cells that are blank. The first option formula you provided needs to see a number in at least 5 cells. The second formula you gave me does not consider a blank cell as 0.

I tried defining all of the cells as number, no decimal places hoping the formula would interpret a blank cell as a 0 - that did not work.

The easy solution would of course be to put 0s in all the blank cells. However, there is a difference between blank cells and cells with 0s. Blank cells indicate the racer did not show for the day. 0s in a cell usually means the racer was disqualified since our point system ensures every racer gets points for participating in a final race.

Any other suggestions?

By the way, your solution has given me many ideas for other points spreadsheets I work with - thanks again!

_________________
Bob

r.beaudet@videotron.ca

[ This Message was edited by: Team BoMart on 2002-02-18 18:47 ]

4. Juan Pablo, this looks familiar. Who was the golf handicapper?
Richard

5. Unless you have another suggestion, I have come up with this work around:

1. Unclick "zero values" in /tools/options/view
2. At the beginning of the season, enter 0s in all fields
3. Enter points in the proper places as the season progresses.
4. Enter DQ for disqualified drivers and hope no one gets disqualified more than twice. If they do, we can readjust the formula for those few cases.

This also solves another problem I just encounterred with the new points series that started last Sunday - Until at least five cells have a numerical value, the formula results in the #NUM! error. By having all cells with 0s, the formula works well and since the "zero values" option is unchecked, the table looks clear.

6. ugly, but this should work
=IF(COUNT(A1:G1)>2,SUM(A1:G1)-(MIN(A1:G1)*(COUNT(A1:G1)>6))-(SMALL(A1:G1,2)*(COUNT(A1:G1)>5)),SUM(A1:G1))

good luck

7. I am getting some errors in some cases.

I am not quite familiar enough with Excel to completely understand the logic. Shooting my best shot, this is what I come up with:

Should the first statement not be =IF(COUNT(A1:G1)>5 to find the comditions where there are numbers in six or seven of the collumns? The last part of the IF statement (the "else") simply sums all of the values across the row and this is correct based on the condition that there are 5 or less cells with values.

When the IF statement is satisfied, "then" there are values in at least six of the seven cells. So you sum all of the cells and then subtract the smallest value of all of the cells and then subtract another value, I assume the next smallest value. _ I just can't quite figure that part of the logic out. I think I am getting confused with the additional COUNT statements.

8. What data are using when you get errors? The true part of the if statement tested fine for me using boolean logic to subtract the lowest two if there were six or seven. The only error can if there were less than three numbers, thus the less than 2 part. Alternatively, you can see if there are five or less just add those. Mine was just kind of a retro fit to avoid an error if there were 0, 1, or 2 numbers the boolean logic couldn't handle.

9. I can e-mail you the spreadsheet if you wish?

10. On 2002-02-19 07:58, Team BoMart wrote:
I can e-mail you the spreadsheet if you wish?
Sure, if you wish. I can look at tonight from home. Or just give me one or two of the lines the formula failed on and I can try to trouble shoot. (ie) 1,blank,3,4,5,blank,7

## User Tag List

#### Posting Permissions

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