Lowest 5 numbers in data base column

[riaosu]

I am working with a data base where I compute a golf handicap based o
each individual's lowest 5 scores from the last eight rounds played.
The data are arranged in columns showing date played and score posted.
Not all dates have a score for each person. Can someone provide
method for determining handicap based only on the lowest 5 rounds

 

[Bob Phillips]

This formula adds up the lowest 5 scores

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

should get you started

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Ron Rosenfeld]

I am working with a data base where I compute a golf handicap based on
each individual's lowest 5 scores from the last eight rounds played.
The data are arranged in columns showing date played and score posted.
Not all dates have a score for each person. Can someone provide a
method for determining handicap based only on the lowest 5 rounds?

Getting the average of the lowest five is easy:

=sum(small(b:b,{1,2,3,4,5}))/5


But I think you want the average of the lowest five out of the most recent
eight, don't you?

Assuming the scores are entered in Column B in order,then the array formula:

=SUM(SMALL(OFFSET(B1,LARGE(ISNUMBER(B1:B100)*
ROW(B1:B100),{1,2,3,4,5,6,7,8})-1,0),{1,2,3,4,5}))/5

will do that. To enter an array formula, hold down <ctrl><shift> while hitting
<enter>. XL will place braces {...} around the formula.


--ron

 

[riaosu]

Bob and Ron

Many thanks for your help--I am fairly new at Excel and am trying t
develop a program for our golf league. Ron, you were right in sayin
that I only want the lowest five scores of the LAST eight posted. Yo
may see other requests from me on this forum as I get more involved i
the project. Thanks again!

Ra