Lowest 5 numbers in data base column

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?


---
Message posted from http://www.ExcelForum.com/

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)

"riaosu " wrote in message
...
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?


---
Message posted from http://www.ExcelForum.com/

On Sun, 13 Jun 2004 16:34:48 -0500, riaosu
wrote:

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 ctrlshift while hitting
enter. XL will place braces {...} around the formula.


--ron

Bob and Ron

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

Ray


---
Message posted from http://www.ExcelForum.com/