Averaging best 15 out of 20?

I have a spreadsheet set up with test scores and I want to average the best
15 scores out of 20 without manually picking the 15 scores. Basically I want
to drop the lowest 5 scores for each student. Is there a formula/function
that does this?

Thanks,
Rando

This is an array formula, meaning you commit it with Ctrl-Shift-Enter

=AVERAGE(LARGE(A1:A20,ROW(1:15)))

where the scores are in A1:A20

"Rando" wrote:

> I have a spreadsheet set up with test scores and I want to average the best
> 15 scores out of 20 without manually picking the 15 scores. Basically I want
> to drop the lowest 5 scores for each student. Is there a formula/function
> that does this?
>
> Thanks,
> Rando
>
>

One slight adjustment that will prevent the formula changing if rows are
inserted or deleted:

=AVERAGE(LARGE(A1:A20,ROW(INDIRECT("1:15"))))


In article <[email protected]>,
"Duke Carey" <[email protected]> wrote:

> This is an array formula, meaning you commit it with Ctrl-Shift-Enter
>
> =AVERAGE(LARGE(A1:A20,ROW(1:15)))
>
> where the scores are in A1:A20
>
> "Rando" wrote:
>
> > I have a spreadsheet set up with test scores and I want to average the best
> > 15 scores out of 20 without manually picking the 15 scores. Basically I
> > want
> > to drop the lowest 5 scores for each student. Is there a formula/function
> > that does this?
> >
> > Thanks,
> > Rando
> >
> >