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.
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.
Hi!
=SUM(LARGE(A1:A20,{1,2,3}))
Biff
>-----Original Message-----
>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.
>.
>
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
emac_mommy wrote:
> 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.
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
"Debra Dalgleish" <[email protected]> wrote...
>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?
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?
--
Regards,
RD
---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Harlan Grove" <[email protected]> wrote in message
news:%[email protected]...
> "Debra Dalgleish" <[email protected]> wrote...
> >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?
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks