Sum total of a group, excluding 4 lowest values in Championship Table
Sum total of a group, excluding 4 lowest values in Championship Table
(OP)
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
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





RE: Sum total of a group, excluding 4 lowest values in Championship Table
RE: Sum total of a group, excluding 4 lowest values in Championship Table
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.
RE: Sum total of a group, excluding 4 lowest values in Championship Table
RE: Sum total of a group, excluding 4 lowest values in Championship Table
Good Luck
johnwm
________________________________________________________
To get the best from these forums read FAQ731-376 before posting
Steam Engine enthusiasts: www.essexsteam.co.uk
RE: Sum total of a group, excluding 4 lowest values in Championship Table
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
RE: Sum total of a group, excluding 4 lowest values in Championship Table
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.
RE: Sum total of a group, excluding 4 lowest values in Championship Table
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.
RE: Sum total of a group, excluding 4 lowest values in Championship Table
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?
RE: Sum total of a group, excluding 4 lowest values in Championship Table
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Sum total of a group, excluding 4 lowest values in Championship Table
http:
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Sum total of a group, excluding 4 lowest values in Championship Table
Good Luck
johnwm
________________________________________________________
To get the best from these forums read FAQ731-376 before posting
Steam Engine enthusiasts: www.essexsteam.co.uk