Performed some more testing and still have a flaw some where. After changing
gameCountFoCalc to 27 the averge is calculated on all values not just the
best 4 of the last 8. Do we need to add a COUNT of valid values by Row some
how?

Darn...

"The Hawk" wrote:

That's it! Thanks Tom...

"Tom Ogilvy" wrote:

Change

gameCountFoCalc = 8 'for 8 weeks

to

gameCountFoCalc = 27 'for 27 weeks

--
Regards,
Tom Ogilvy


"The Hawk" wrote in message
...
Thanks Tom. I loaded the code and when trying to run received #NUM in
every
cell in the AVG column. Checked the Reg_Scores Worksheet AVG column and
found that the Array loaded was:

{=AVERAGE(SMALL(IF($V5:$AC50,$V5:$AC5),{1,2,3,4}) )}

As there was no data in $V5:$AC5 changed it in one of the cells to
$C5:$AC5
which captured all scores entered and everything worked. It seems the
code
set just the last 8 columns before HDCP; however, I'm not sure what to do
with it to ensure it picks the last 8 cells with valid scores (0).

Thanks...


"Tom Ogilvy" wrote:

I didn't change this comment:

gameCountFoCalc = 7 'for 8 weeks

but in my implementation, for 8 weeks, you would set it to 8

gameCountFoCalc = 8 'for 8 weeks

The latest number of weeks you want to consider.

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
Make sure you set gameCountFoCalc to the number of games to be
considered
in
the average.

If there are fewer than 4 scores in that range of columns from the
average
column then it will display #Num


Sub CalcAvg_HDCP()
Dim gameCountFoCalc As Long
Dim rng As Range, rng1 As Range
Dim rng2 As Range, colStart As Long
Dim colEnd As Long, s As String
Dim cell As Range

' Set number of game to use in calc and redefine array (Total -1)
gameCountFoCalc = 7 'for 8 weeks

Worksheets("Reg_Scores").Activate

' Locate the last column, based on the cell containing (HDCP) in
row 2
Set rng = Rows(2).Find(What:="HDCP", After:=Range("A2"), _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
Set rng = rng.Offset(0, -1)
Else
MsgBox "HDCP not found"
Exit Sub
End If

' Locate the last row based on the
' cell containing (<End Players) in column 2
Set rng1 = Columns(2).Find(What:="End Players", _
After:=Range("B2"), _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
If rng1 Is Nothing Then
MsgBox "End Players not found"
Exit Sub
Else
Set rng1 = rng1.Offset(-1, 0)
End If

' Clear HDCP column prior to new calculations
Set rng2 = rng.Offset(1, 1).Resize(rng1.Row - 2)
rng2.ClearContents

' Loop thru a row/column and get the last numbers for calc an avg.
colStart = (rng2.Column - 1) - gameCountFoCalc
colEnd = rng2.Column - 2
s = "RC" & colStart & ":RC" & colEnd
For Each cell In rng2.Offset(0, -1)
cell.FormulaArray = "=AVERAGE(SMALL(IF(" & s & "0," & _
s & "),{1,2,3,4}))"
Next
rng2.FormulaR1C1 = "=(RC[-1]-31)*.8"
End Sub

--
Regards,
Tom Ogilvy


"The Hawk" wrote in message
...
Here's a sample of the sheet:

TM PLAYER 0 1 20 AVG HDCP
1 Player 1 0 39 34.8 3
1 Player 2 0 gb (25)
1 Player 3 0 38 (25)
1 Player 4 0 37 (25)
2 Player 1 0 36 (25)
2 Player 2 0 35 (25)
2 Player 3 0 43 (25)
2 Player 4 0 lr (25)
3 Player 1 0 ks (25)

<End Players

First column is TEAM. I use this for sorts along with the AVG to
determine
playing position (i.e., 1, 2, 3, 4) for each week. Columns 0, 1 ...
20
indicate weeks. There are 6 columns before Week 0 which are the last
6
scores
from the prior year. HDCP is calculated based on a formula
(=<AVG-31*.8).
Our league plays on a par 62 (31 for 9) Executive Course.

Thanks...

"martin" wrote:

Would be easier to help you if you gave more details on the layout
of
the spreadsheet. What is there besides golf scores? I got that
"<End
Players" appears in column B of the row after the last row of
scores
and that "HDCP" appears in row 2 of the column after the column
containing the last score. Also (I don't play much golf) how is
handicap calculated? 67 minus average?