Summing top 5 of 7 values

Team BoMart · Feb 18th, 2002, 07:09 PM

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?

Juan Pablo González · Feb 18th, 2002, 07:22 PM

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}))

Team BoMart · Feb 18th, 2002, 07:45 PM

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 ]

RichardS · Feb 18th, 2002, 09:12 PM

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

Team BoMart · Feb 18th, 2002, 09:27 PM

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.

IML · Feb 18th, 2002, 09:41 PM

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

Team BoMart · Feb 18th, 2002, 10:50 PM

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.

IML · Feb 19th, 2002, 08:28 AM

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.

Team BoMart · Feb 19th, 2002, 08:58 AM

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

IML · Feb 19th, 2002, 09:11 AM

Quote:

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

Feb 18th, 2002, 07:22 PM	#2
Juan Pablo González MrExcel MVP Join Date: Feb 2002 Location: Bogota, Colombia Posts: 11,927	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})) __________________ Regards, Juan Pablo González http://www.juanpg.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 18th, 2002, 07:09 PM	#1
Team BoMart New Member Join Date: Feb 2002 Location: Ottawa, Ontario, Canada Posts: 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/rc...er20012002.htm The question: How can I sum the top 5 values across a row of 7 numbers?

Feb 18th, 2002, 07:45 PM	#3
Team BoMart New Member Join Date: Feb 2002 Location: Ottawa, Ontario, Canada Posts: 12	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 ]

Feb 18th, 2002, 09:12 PM	#4
RichardS Board Regular Join Date: Feb 2002 Location: Victoria, Australia Posts: 761	Juan Pablo, this looks familiar. Who was the golf handicapper? Richard

Feb 18th, 2002, 09:27 PM	#5
Team BoMart New Member Join Date: Feb 2002 Location: Ottawa, Ontario, Canada Posts: 12	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.

Feb 18th, 2002, 09:41 PM	#6
IML MrExcel MVP Join Date: Feb 2002 Location: Denver, CO Posts: 1,744	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

Feb 18th, 2002, 10:50 PM	#7
Team BoMart New Member Join Date: Feb 2002 Location: Ottawa, Ontario, Canada Posts: 12	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.

Feb 19th, 2002, 08:28 AM	#8
IML MrExcel MVP Join Date: Feb 2002 Location: Denver, CO Posts: 1,744	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.

Feb 19th, 2002, 08:58 AM	#9
Team BoMart New Member Join Date: Feb 2002 Location: Ottawa, Ontario, Canada Posts: 12	I can e-mail you the spreadsheet if you wish?