Average after dropping two lowest scores

I am trying to take the average of a series of items after dropping the two
lowest scores. This is trivial if I want to drop only the lowest score (use
the MIN function). However, I do not know of a simple way, with an Excel
function for example, to take the average or sum of a set of numbers if I
want to drop the 2 or three lowest scores. Thanks,
Jim

Hi
use the SMALL function. e.g.
=SMALL(your_range,2)
for the second lowest number in your range

--
Regards
Frank Kabel
Frankfurt, Germany
"jsl" <nospam@nospam.com> schrieb im Newsbeitrag
news:ep7WAhiAEHA.464@TK2MSFTNGP11.phx.gbl...
> I am trying to take the average of a series of items after dropping
the two
> lowest scores. This is trivial if I want to drop only the lowest
score (use
> the MIN function). However, I do not know of a simple way, with an
Excel
> function for example, to take the average or sum of a set of numbers
if I
> want to drop the 2 or three lowest scores. Thanks,
> Jim
>
>

Thanks!

"Frank Kabel" <frank.kabel@freenet.de> wrote in message
news:Oa51xmiAEHA.2404@TK2MSFTNGP11.phx.gbl...
> Hi
> use the SMALL function. e.g.
> =SMALL(your_range,2)
> for the second lowest number in your range
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
> "jsl" <nospam@nospam.com> schrieb im Newsbeitrag
> news:ep7WAhiAEHA.464@TK2MSFTNGP11.phx.gbl...
> > I am trying to take the average of a series of items after dropping
> the two
> > lowest scores. This is trivial if I want to drop only the lowest
> score (use
> > the MIN function). However, I do not know of a simple way, with an
> Excel
> > function for example, to take the average or sum of a set of numbers
> if I
> > want to drop the 2 or three lowest scores. Thanks,
> > Jim
> >
> >
>

One way (Array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=AVERAGE(LARGE(OFFSET(A1,,,n,1), ROW(INDIRECT("1:" & n - y))))

where you should replace n with the number of rows in your range and y
with the number of scores to drop.

In article <ep7WAhiAEHA.464@TK2MSFTNGP11.phx.gbl>,
"jsl" <nospam@nospam.com> wrote:

> I am trying to take the average of a series of items after dropping the two
> lowest scores. This is trivial if I want to drop only the lowest score (use
> the MIN function). However, I do not know of a simple way, with an Excel
> function for example, to take the average or sum of a set of numbers if I
> want to drop the 2 or three lowest scores. Thanks,
> Jim
>
>

So the formula for a range D1 to D6 for instance would be.......
=SUM(SUM(D16)-SMALL(D16,1)-SMALL(D16,2))/(COUNT(D16)-2)
if there are more than one second smallest number
then only one of them has been subtracted

or if there are three smallest numbers the same
then only two of them get subtracted.

regards
Bill K

"Frank Kabel" <frank.kabel@freenet.de> wrote in message
news:Oa51xmiAEHA.2404@TK2MSFTNGP11.phx.gbl...
> Hi
> use the SMALL function. e.g.
> =SMALL(your_range,2)
> for the second lowest number in your range
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
> "jsl" <nospam@nospam.com> schrieb im Newsbeitrag
> news:ep7WAhiAEHA.464@TK2MSFTNGP11.phx.gbl...
> > I am trying to take the average of a series of items after dropping
> the two
> > lowest scores. This is trivial if I want to drop only the lowest
> score (use
> > the MIN function). However, I do not know of a simple way, with an
> Excel
> > function for example, to take the average or sum of a set of numbers
> if I
> > want to drop the 2 or three lowest scores. Thanks,
> > Jim
> >
> >
>