picking the best 5 out of 10
 

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

picking the best 5 out of 10
 

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



"john cornell" schreef in bericht
...
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

picking the best 5 out of 10
 

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.

"moon" wrote in message
. ..
Does that mean that your highest value is a -value?



"john cornell" schreef in bericht
...
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

picking the best 5 out of 10
 

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


"john cornell" wrote in message
...
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

picking the best 5 out of 10
 

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


"john cornell" wrote in message
...
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

picking the best 5 out of 10
 

Have you tried this site?
http://www.dailydoseofexcel.com/arch...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


"john cornell" wrote in message
...
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

picking the best 5 out of 10
 

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.
"JLGWhiz" wrote in message
...
Have you tried this site?
http://www.dailydoseofexcel.com/arch...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


"john cornell" wrote in message
...
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

picking the best 5 out of 10
 

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(C 12: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:C 21,3),SMALL(C12:C21,4),SMALL(C12:C21,5))

Can you make this idea work?

Pieter Vandenberg

john cornell wrote:
: 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.
: "JLGWhiz" wrote in message
: ...
: Have you tried this site?
: http://www.dailydoseofexcel.com/arch...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
:
:
: "john cornell" wrote in message
: ...
: 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
:
:
:
:
:
:

picking the best 5 out of 10
 

THANKS IT WORKS FINE!
"vandenberg p" wrote in message
...
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(C 12: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:C 21,3),SMALL(C12:C21,4),SMALL(C12:C21,5))

Can you make this idea work?

Pieter Vandenberg

john cornell wrote:
: 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.
: "JLGWhiz" wrote in message
: ...
: Have you tried this site?
: http://www.dailydoseofexcel.com/arch...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
:
:
: "john cornell" wrote in message
: ...
: 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
:
:
:
:
:
: