Thread: Average of lowest 5 of 20 values?

The following are what golf handicappers call differential values. I've
listed 25 of them. To compute my golf score handicap I need to take the
average of the lowest 5 of the last 20 of these values, and multiply that by
0.96.
The way I see it, the lowest 5 are 5.55, 6.59, 6.59, 6.59, and 7.64. The
average of these 5 would be 6.6.
Any way I could use my Excel 2003 with some formula?

11.82
13.92
14.96
8.68
17.05
19.15
11.82
13.92
5.55
7.64
14.96
12.87
6.59
11.82
9.73
9.73
6.59
17.05
14.96
6.59
9.73
8.68
7.64
13.92
7.64

Len Miller
--
To email reply, eradicate all threes in my SPAM guarded address.

One possible way

=SUM(SMALL(A1:A20,{1,2,3,4,5}))/5

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Fatfreek" <miller3lr@oecc3wildblue3.com> wrote in message
news:qOadnUVlpKKglQ_ZnZ2dnUVZ_rudnZ2d@trueband.net...
> The following are what golf handicappers call differential values. I've
> listed 25 of them. To compute my golf score handicap I need to take the
> average of the lowest 5 of the last 20 of these values, and multiply that
> by
> 0.96.
> The way I see it, the lowest 5 are 5.55, 6.59, 6.59, 6.59, and 7.64. The
> average of these 5 would be 6.6.
> Any way I could use my Excel 2003 with some formula?
>
> 11.82
> 13.92
> 14.96
> 8.68
> 17.05
> 19.15
> 11.82
> 13.92
> 5.55
> 7.64
> 14.96
> 12.87
> 6.59
> 11.82
> 9.73
> 9.73
> 6.59
> 17.05
> 14.96
> 6.59
> 9.73
> 8.68
> 7.64
> 13.92
> 7.64
>
> Len Miller
> --
> To email reply, eradicate all threes in my SPAM guarded address.
>
>

Hi
Try:

=AVERAGE(SMALL(A1:A25,{1,2,3,4,5}))

will do the trick

HTH
Michael M

"Fatfreek" wrote:

> The following are what golf handicappers call differential values. I've
> listed 25 of them. To compute my golf score handicap I need to take the
> average of the lowest 5 of the last 20 of these values, and multiply that by
> 0.96.
> The way I see it, the lowest 5 are 5.55, 6.59, 6.59, 6.59, and 7.64. The
> average of these 5 would be 6.6.
> Any way I could use my Excel 2003 with some formula?
>
> 11.82
> 13.92
> 14.96
> 8.68
> 17.05
> 19.15
> 11.82
> 13.92
> 5.55
> 7.64
> 14.96
> 12.87
> 6.59
> 11.82
> 9.73
> 9.73
> 6.59
> 17.05
> 14.96
> 6.59
> 9.73
> 8.68
> 7.64
> 13.92
> 7.64
>
> Len Miller
> --
> To email reply, eradicate all threes in my SPAM guarded address.
>
>
>

Michael,

I also tried your solution and it works. Thanks.

Len

--
To email reply, eradicate all threes in my SPAM guarded address.
"Michael M" <MichaelM@discussions.microsoft.com> wrote in message
news:59894EDF-4865-4A2E-9E04-51EEB0D48EA8@microsoft.com...
> Hi
> Try:
>
> =AVERAGE(SMALL(A1:A25,{1,2,3,4,5}))
>
> will do the trick
>
> HTH
> Michael M
>
> "Fatfreek" wrote:
>
> > The following are what golf handicappers call differential values. I've
> > listed 25 of them. To compute my golf score handicap I need to take the
> > average of the lowest 5 of the last 20 of these values, and multiply
that by
> > 0.96.
> > The way I see it, the lowest 5 are 5.55, 6.59, 6.59, 6.59, and 7.64. The
> > average of these 5 would be 6.6.
> > Any way I could use my Excel 2003 with some formula?
> >
> > 11.82
> > 13.92
> > 14.96
> > 8.68
> > 17.05
> > 19.15
> > 11.82
> > 13.92
> > 5.55
> > 7.64
> > 14.96
> > 12.87
> > 6.59
> > 11.82
> > 9.73
> > 9.73
> > 6.59
> > 17.05
> > 14.96
> > 6.59
> > 9.73
> > 8.68
> > 7.64
> > 13.92
> > 7.64
> >
> > Len Miller
> > --
> > To email reply, eradicate all threes in my SPAM guarded address.
> >
> >
> >

Peo,

Thanks very much. That works perfect.

Len

--
To email reply, eradicate all threes in my SPAM guarded address.
"Peo Sjoblom" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
news:OjbkVYOkGHA.4716@TK2MSFTNGP03.phx.gbl...
> One possible way
>
> =SUM(SMALL(A1:A20,{1,2,3,4,5}))/5
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> Excel 95 - Excel 2007
> Northwest Excel Solutions
> www.nwexcelsolutions.com
> "It is a good thing to follow the first law of holes;
> if you are in one stop digging." Lord Healey
>
>
> "Fatfreek" <miller3lr@oecc3wildblue3.com> wrote in message
> news:qOadnUVlpKKglQ_ZnZ2dnUVZ_rudnZ2d@trueband.net...
> > The following are what golf handicappers call differential values. I've
> > listed 25 of them. To compute my golf score handicap I need to take the
> > average of the lowest 5 of the last 20 of these values, and multiply
that
> > by
> > 0.96.
> > The way I see it, the lowest 5 are 5.55, 6.59, 6.59, 6.59, and 7.64. The
> > average of these 5 would be 6.6.
> > Any way I could use my Excel 2003 with some formula?
> >
> > 11.82
> > 13.92
> > 14.96
> > 8.68
> > 17.05
> > 19.15
> > 11.82
> > 13.92
> > 5.55
> > 7.64
> > 14.96
> > 12.87
> > 6.59
> > 11.82
> > 9.73
> > 9.73
> > 6.59
> > 17.05
> > 14.96
> > 6.59
> > 9.73
> > 8.68
> > 7.64
> > 13.92
> > 7.64
> >
> > Len Miller
> > --
> > To email reply, eradicate all threes in my SPAM guarded address.
> >
> >
>
>

Thanks for the feedback, glad something met your needs

Regards
Michael M

"Michael M" wrote:

> Hi
> Try:
>
> =AVERAGE(SMALL(A1:A25,{1,2,3,4,5}))
>
> will do the trick
>
> HTH
> Michael M
>
> "Fatfreek" wrote:
>
> > The following are what golf handicappers call differential values. I've
> > listed 25 of them. To compute my golf score handicap I need to take the
> > average of the lowest 5 of the last 20 of these values, and multiply that by
> > 0.96.
> > The way I see it, the lowest 5 are 5.55, 6.59, 6.59, 6.59, and 7.64. The
> > average of these 5 would be 6.6.
> > Any way I could use my Excel 2003 with some formula?
> >
> > 11.82
> > 13.92
> > 14.96
> > 8.68
> > 17.05
> > 19.15
> > 11.82
> > 13.92
> > 5.55
> > 7.64
> > 14.96
> > 12.87
> > 6.59
> > 11.82
> > 9.73
> > 9.73
> > 6.59
> > 17.05
> > 14.96
> > 6.59
> > 9.73
> > 8.68
> > 7.64
> > 13.92
> > 7.64
> >
> > Len Miller
> > --
> > To email reply, eradicate all threes in my SPAM guarded address.
> >
> >
> >