About Us

The Hawk

Tom,
That did it!

Thanks so much...

"Tom Ogilvy" wrote:

Try this one:

Sub CalcAvg_HDCP1()
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, s1 As String
Dim s2 As String

' Set number of game to use in calc and redefine array (Total -1)
gameCountFoCalc = 27 'for 27 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
s1 = "=IF(COUNT(IF(XX0,XX))=8,AVERAGE(SMALL" & _
"(IF(IF(COLUMN(XX)=LARGE(IF(XX0,COLUMN(XX)),8),1 ,0)" & _
"*IF(XX0,XX)0,XX),{1,2,3,4})),AVERAGE(SMALL" & _
"(IF(XX0,XX),{1,2,3,4})))"
s2 = Application.Substitute(s1, "XX", s)
For Each cell In rng2.Offset(0, -1)
cell.FormulaArray = s2
Next
rng2.FormulaR1C1 = "=(RC[-1]-31)*.8"
End Sub

--
Regards,
Tom Ogilvy

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Error calculation	Vyacheslav	Excel Discussion (Misc queries)	2	November 14th 09 12:56 PM
Calculation error?	silver	Excel Discussion (Misc queries)	1	October 26th 07 04:36 PM
Calculation Error?	Wei Ming from singapore	Excel Discussion (Misc queries)	3	January 3rd 07 12:39 PM
Re-Calculation error	5 Star	Excel Discussion (Misc queries)	2	June 16th 05 05:05 PM
calculation error	happyearth	Excel Worksheet Functions	2	June 16th 05 05:59 AM

Menu

About Us