Sum total of a group, excluding 4 lowest values in Championship Table 1

[Engineeeeeeeeer]

I have a championship table. The heading of the top row is Round 1, Round 2, Round 3 etc up to Round 10 - located in Row 3, columns D through to M.
Column A, Rows 3 to 15 has Position (i.e. 1, 2 3 4 etc), column B (same rows) has total points; Column C has the Championship Contenders name. Out of the 10 rounds, each contender can drop the 4 lowest round scores.

Now for the question. Is there a function that will sum the values of the rows (Points from each round) and automatically detect and deduct the lowest four values, and give the championship total for each row (contender). (This total will then be used to sort the contender order - highest at the top).

One more thing, the function will have to be so that it does not need all 10 round scores (values) to be present, as the contender may not have attended all rounds, or those rounds haven't taken place yet.

Any ideas & suggestions would be gratefully received.

Thanks

 

[SacreBleu]

The SUMIF function may be what you need.

 

[electricpete]

Data in A1:A10

Your formula is

=+SUM(A1:A10)-SMALL(A1:A10,1)-SMALL(A1:A10,2))-SMALL(A1:A10,3)-SMALL(A1:A10,4)


=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[SacreBleu]

I found a Cricket Championship excell spreadsheet. It may give some other ideas for your table.

 

[johnwm]

Very helpful, SacreBleu - care to give us a link?

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

Steam Engine enthusiasts:

http://www.essexsteam.co.uk

 

[Engineeeeeeeeer]

Thanks for your help.  Very useful - I've now made a macro that sorts the table into the correct (descending) order.

One more thing - is there any way the function can be modified to put brackets around the four lowest results so that it is clear which results have been excluded from the total.

Once again - thanks

 

[electricpete]

With your data in A1:A10 as before

Put your cursor on A1 and select Format/ConditionalFormatting


Change the pulldown box on the left from "cell value is" to "formula is"

Paste the following into the formula box on the right
=+OR(A1=SMALL($A$1:$A$10,1),A1=SMALL($A$1:$A$10,2),A1=SMALL($A$1:$A$10,3),A1=SMALL($A$1:$A$10,4))

Select Format / Patterns and pick the shade grey

Click ok/ok to accept

Now with cursor still in cell A1 select the "Format painter" button (looks like a paintbrush). It should put cell 1 in a dashed line. Drag a range from A2:A10

That applies the same format to the remaining cells.

You should now have your lowest four scores in grey.










=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

If your 4th lowest and 5th lowest scores are tied, you will have some problems with this approach.

If it became a problem, you can break all ties by adding +0.0001*row() to each score. Then adjust your number format so that fraction doesn't show.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[SacreBleu]

johnwm,
Sorry -I meant I found it on my computer (had obtained it from a friend years ago). I am somewhat Internet-challenged - is there a way I can post it to some generic website?

 

[electricpete]

If you email it to me, I can post it. My email is on my profile

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

Here it is:

http://home.houston.rr.com/electricpete/eng-tips/CricketWC200.xls

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[johnwm]

Thanks guys. Let's hope it's useful to Engineeeeeeeeer

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

Steam Engine enthusiasts:

http://www.essexsteam.co.uk