Summing top 5 of 7 values

Team BoMart

New Member
Joined
Feb 17, 2002
Messages
12
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/rcottawa/winter20012002.htm

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

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top