Summing top 5 of 7 values

[Team BoMart]

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?

 

[Team BoMart]

I have sent the file. The first failure is in the first line. The values for the cells in questions are:

50 48 Blank 49 47 45 46

The formula calculates this as 239 instead of 240.

The next error occurs on the 6th row:

Blank 45 48 44 43 38 41

The formula calculates this as 218 instead of 221.

It works properly for all remaining rows in the top section of the spreadsheet.

The formula works well with >2 or >5 used in the first argument. It also works well using either >2 or >5 for rows that contain a value in only one cell and the rest blank.

In reviewing the table further, the formula only seems to fail when there is only one blank cell in the row. If there are no blank cells, it works well and if there are 2 or more blank cells it works well.

_________________
Bob, feel free to<a href=mailto:r.beaudet@videotron.ca>E-Mail me</a>
Also, check out the<a href=HTTP://www.igs.net/~rbeaudet/NCMA/rcottawa>Club Home Page</a> or la<a href=HTTP://www.igs.net/~rbeaudet/NCMA/HomePage/Main.htm>Page d'acceuil du club
This message was edited by Team BoMart on 2002-02-19 08:48

 

[IML]

On 2002-02-19 08:41, Team BoMart wrote:
I have sent the file. The first failure is in the first line. The values for the cells in questions are:

50 48 Blank 49 47 45 46

The formula calculates this as 239 instead of 240.

The next error occurs on the 6th row:

Blank 45 48 44 43 38 41

The formula calculates this as 218 instead of 221.

It works properly for all remaining rows in the top section of the spreadsheet.

The formula works well with >2 or >5 used in the first argument. It also works well using either >2 or >5 for rows that contain a value in only one cell and the rest blank.

In reviewing the table further, the formula only seems to fail when there is only one blank cell in the row. If there are no blank cells, it works well and if there are 2 or more blank cells it works well.

_________________
Bob, feel free to<a href=mailto:r.beaudet@videotron.ca>E-Mail me</a>
Also, check out the<a href=HTTP://www.igs.net/~rbeaudet/NCMA/rcottawa>Club Home Page</a> or la<a href=HTTP://www.igs.net/~rbeaudet/NCMA/HomePage/Main.htm>Page d'acceuil du club
This message was edited by Team BoMart on 2002-02-19 08:48

I'll defer to some else to make it pretty, but
=IF(COUNT(A1:G1)<=5,SUM(A1:G1),IF(COUNT(A1:G1)=6,SUM(A1:G1)-MIN(A1:G1),SUM(A1:G1)-MIN(A1:G1)-SMALL(A1:G1,2)))

should address your needs.

 

[Team BoMart]

That works very well!
I think I can figure this one out as well:

If there are 5 or less cells with values then sum the cells.

Else If there are 6 cells with values then sum the cells and subtract the smallest value.

Else Sum the cells, subtract the minimum value and subtract the second smallest value.

Is that correct?

Thanks for all of your help.

 

[Aladin Akyurek]

On 2002-02-19 08:53, IML wrote:

On 2002-02-19 08:41, Team BoMart wrote:
I have sent the file. The first failure is in the first line. The values for the cells in questions are:

50 48 Blank 49 47 45 46

The formula calculates this as 239 instead of 240.

The next error occurs on the 6th row:

Blank 45 48 44 43 38 41

The formula calculates this as 218 instead of 221.

It works properly for all remaining rows in the top section of the spreadsheet.

The formula works well with >2 or >5 used in the first argument. It also works well using either >2 or >5 for rows that contain a value in only one cell and the rest blank.

In reviewing the table further, the formula only seems to fail when there is only one blank cell in the row. If there are no blank cells, it works well and if there are 2 or more blank cells it works well.

_________________
Bob, feel free to<a href=mailto:r.beaudet@videotron.ca>E-Mail me</a>
Also, check out the<a href=HTTP://www.igs.net/~rbeaudet/NCMA/rcottawa>Club Home Page</a> or la<a href=HTTP://www.igs.net/~rbeaudet/NCMA/HomePage/Main.htm>Page d'acceuil du club
This message was edited by Team BoMart on 2002-02-19 08:48

I'll defer to some else to make it pretty, but
=IF(COUNT(A1:G1)<=5,SUM(A1:G1),IF(COUNT(A1:G1)=6,SUM(A1:G1)-MIN(A1:G1),SUM(A1:G1)-MIN(A1:G1)-SMALL(A1:G1,2)))

should address your needs.

Ian,

I didn't follow the question closely, but wouldn't

=IF(COUNT(A1:G1)>=5,SUMPRODUCT(LARGE(A1:G1,{1,2,3,4,5})),"Not Enough Data")

suffice?

Aladin

 

[IML]

Almost perfect. I started on the wrong path and kept going. To meet the critera, I think a simple
=IF(COUNT(A1:G1)>5,SUMPRODUCT(LARGE(A1:G1,{1,2,3,4,5})),SUM(A1:G1))

will do the trick. Much cleaner.

 

[Team BoMart]

Sorry for the delay, I was away.
That works just great! And it is super clean!
I guess I have allot to learn!! Thank you all for your assistance.

 

[Team BoMart]

I have a question:

Why do you use "SUMPRODUCT" rather than just "SUM"? "SUM" does seem to work.

 

[Aladin Akyurek]

On 2002-02-20 05:46, Team BoMart wrote:
I have a question:

Why do you use "SUMPRODUCT" rather than just "SUM"? "SUM" does seem to work.

You're absolutely right: LARGE produces an array of values that SUM can total, so SUMPRODUCT is not necessary to force LARGE (that was the idea behind its 'blind' use) to compute an array of values.

And, thanks pointing that out.

Aladin

 

[Team BoMart]

Thanks!