picking the best 5 out of 10

[john cornell]

I need to pick the best five scores out of ten scores. I am doing this for a
golf league. After i find a way to do this i can set up the rest of the
formula for the handicap.

thanks in advance

 

[moon]

Does that mean that your highest value is a -value?

 

[john cornell]

I have golf scores that might range for one individual from 85 to 95 over a
period of ten plays. i need to have a way to pick the ten best scores.

i hope that answers your question.

 

[Gary Keramidas]

here's a long formula i use to average the lowest 3 scores out of the last 5
rounds. maybe it will help. it's an array formula in row 9, in this case:

=IF(ISBLANK(E9),"",AVERAGE(SMALL(IF(OFFSET(B9:T9,0,MATCH(1E+300,B9:T9)-5,1,5)=0,MAX(B9:T9),OFFSET(B9:T9,0,MATCH(1E+300,B9:T9)-5,1,5)),{1,2,3})))

 

[john cornell]

i wonder is there away to make it five out of 10 by changing the formula.
will try and let you know.

 

[Guest]

Have you tried this site?
http://www.dailydoseofexcel.com/archives/2005/08/18/golf-handicap/

 

[john cornell]

That is not really what i am looking for. I have 80 golfers and need to work
a handicap for all of them.

I think the person was from Norway. Still hoping to find the right formula

Last year some one sent a formula and i can't find it.

 

[vandenberg p]

Hello:

I know nothing about golf handicaps but why don't you just use the small function?
(assume the 10 scores in C12 to C21

The smallest: =SMALL(C12:C21,1)
The 2nd smallest =SMALL(C12:C21,2)
etc.
the 5th smallest =SMALL(C12:C21,5)

You can put these 5 formulas into five helper cells and then manipulate those to
compute the handicap or you can do combined calculations. For example:

To add the 5 smallest:
=(SMALL(C12:C21,1)+SMALL(C12:C21,2)+SMALL(C12:C21,3)+SMALL(C12:C21,4)+SMALL(C12:C21,5))

To average the 5 smallest:
=AVERAGE(SMALL(C12:C21,1),SMALL(C12:C21,2),SMALL(C12:C21,3),SMALL(C12:C21,4),SMALL(C12:C21,5))

To find the standard deviation of the 5 smallest:
=STDEV(SMALL(C12:C21,1),SMALL(C12:C21,2),SMALL(C12:C21,3),SMALL(C12:C21,4),SMALL(C12:C21,5))

To find the the largest of the 5 smallest:
=MAX(SMALL(C12:C21,1),SMALL(C12:C21,2),SMALL(C12:C21,3),SMALL(C12:C21,4),SMALL(C12:C21,5))

Can you make this idea work?

Pieter Vandenberg

: That is not really what i am looking for. I have 80 golfers and need to work
: a handicap for all of them.

: I think the person was from Norway. Still hoping to find the right formula

: Last year some one sent a formula and i can't find it.
: :> Have you tried this site?
:> http://www.dailydoseofexcel.com/archives/2005/08/18/golf-handicap/
:>
:> "john cornell" wrote:
:>
:>> i wonder is there away to make it five out of 10 by changing the formula.
:>> will try and let you know.
:>> "Gary Keramidas" <GKeramidasATmsn.com> wrote in message
:>> :>> > here's a long formula i use to average the lowest 3 scores out of the
:>> > last
:>> > 5 rounds. maybe it will help. it's an array formula in row 9, in this
:>> > case:
:>> >
:>> > =IF(ISBLANK(E9),"",AVERAGE(SMALL(IF(OFFSET(B9:T9,0,MATCH(1E+300,B9:T9)-5,1,5)=0,MAX(B9:T9),OFFSET(B9:T9,0,MATCH(1E+300,B9:T9)-5,1,5)),{1,2,3})))
:>> >
:>> > --
:>> >
:>> >
:>> > Gary
:>> >
:>> >
:>> > :>> >>I need to pick the best five scores out of ten scores. I am doing this
:>> >>for
:>> >>a golf league. After i find a way to do this i can set up the rest of
:>> >>the
:>> >>formula for the handicap.
:>> >>
:>> >> thanks in advance
:>> >>
:>> >
:>> >
:>>
:>>
:>>

 

[john cornell]

THANKS IT WORKS FINE!