Excel, Sum best N numbers in range


Recently I faced an interesting challenge. How to create an Excel formula for calculating the final sum of the best N scores out of M (where N > M)?

The real situation is as follows. In our chess club we have 16 tournaments per year. The winner is the player who scores the most points in 12 tournaments. So, I have to sum the best 12 scores out of 16.

I was trying with different functions. An interesting one was the RANK.AVG function, which returns the rank of value in the specified range. With some additional formulas and a special transformation table I finally succeded. But …. It was to complicated and I was not happy with it.

As it is usualy so, I found another function after spending to much time.

The right function is LARGE or her sister SMALL:

The final formula in may case was:

=SUM(A2:P2)-LARGE(A2:P2;16)-LARGE(A2:P2;15)-LARGE(A2:P2;14)-LARGE(A2:P2;13)

(in the first step I sum all values and then subtract the four worst scores)

About these ads

About Andrej Dobrovoljc

Andrej Dobrovoljc

Posted on 14. December, 2010, in Excel and tagged , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 40 other followers

%d bloggers like this: