×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

The SUMIF function may be what you need.

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

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.

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

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

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

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: www.essexsteam.co.uk

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

(OP)
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

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

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.

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

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.

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

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?

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

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.

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

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: www.essexsteam.co.uk

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close