How do I add only the highest three numbers in a range of data?

[Guest]

I am using Excel to record, add and rank scores for a gymnastics meet. We
have teams of as many as 20 girls, but only the top three scores count
towards a team score. How can I program a SUM function that adds only the
highest three scores.

 

[Biff]

Hi!

=SUM(LARGE(A1:A20,{1,2,3}))

Biff

 

[Debra Dalgleish]

You can use an array formula to total the top scores. For example, with
scores in cells B2:B100 --

=SUM(LARGE(B2:B100,ROW(INDIRECT("1:3"))))

To array enter the formula, hold the Ctrl and Shift keys, then press Enter.

There's an example here:

http://www.contextures.com/xlFunctions01.html#Top5

 

[Harlan Grove]

You can use an array formula to total the top scores. For example, with
scores in cells B2:B100 --

=SUM(LARGE(B2:B100,ROW(INDIRECT("1:3"))))

To array enter the formula, hold the Ctrl and Shift keys, then press Enter.

....

If the number of values to average is small (subjective, but 3 is definitely
a small number), and if you're hardcoding the 2nd arg to LARGE anyway
("1:3"), why not use

=SUM(LARGE(B2:B100,{1,2,3}))

which doesn't have to be entered as an array formula?

 

[Ragdyer]

Are you not receiving all posted messages Harlan?

This is exactly what Biff posted, as a suggestion in his original reply to
this thread ... 2 days ago!

That made me also wonder at Debra's not seeing Biff's post?