VBA Calculation with Variables

[Hawk]

Hello: I'm trying to modify a program I wrote with some help a number of
years ago and find that I have fallen behind in programming thought. The
program currently scans each row (representing a person) and places up to the
last eight >0 values into an array which is then sorted low to high numbers.
In that version the 4 lowest values were summed and an average returned .

The revision is to place up to the last ten >0 values into the array and
sort low to high. This is where the change takes place in that now I want to
calculate an average if there are anywhere from 2 to 8 values. I started
with If.. Then.. Else and it seemed to get complex so I tried Select Case but
my code only works with 8 values. Below is that portion of the code:

Dim golfavg as Single

' calculate average if >=2, <=8 valid scores
golfavg = 0

Select Case 8 - 0
Case 8
If holdnumbers(0) > 0 And holdnumbers(1) > 0 And holdnumbers(2)

0 And holdnumbers(3) > 0 And holdnumbers(4) > 0 And holdnumbers(5) > 0 And

holdnumbers(6) > 0 And holdnumbers(7) > 0 Then
golfavg = (holdnumbers(0) + holdnumbers(1) + holdnumbers(2)
+ holdnumbers(3) + holdnumbers(4) + holdnumbers(5) + holdnumbers(6) +
holdnumbers(7)) / 8
End If

Case 7
If holdnumbers(0) > 0 And holdnumbers(1) > 0 And holdnumbers(2)

0 And holdnumbers(3) > 0 And holdnumbers(4) > 0 And holdnumbers(5) > 0 And

holdnumbers(6) > 0 Then
golfavg = (holdnumbers(0) + holdnumbers(1) + holdnumbers(2)
+ holdnumbers(3) + holdnumbers(4) + holdnumbers(5) + holdnumbers(6)) / 7
End If

....the code continues in this manner through Case 2.

Case 0 - 1
golfavg = 0

End Select

It seems the code is looking strictly at Case 8 and not moving on to the
other Case statements if less than 8 values. This presents another problem
that when there are less than 8 values it returns very large numbers as if
additional numbers are being added to the array or the array is not being
cleared in those cases so I'm assuming that the code is not reaching End
Select even though it runs through.

Range("AI" & j).Value = golfavg
' reset score array
holdnumbers(0) = 0
holdnumbers(1) = 0
holdnumbers(2) = 0
holdnumbers(3) = 0
holdnumbers(4) = 0
holdnumbers(5) = 0
holdnumbers(6) = 0
holdnumbers(7) = 0
holdnumbers(8) = 0
holdnumbers(9) = 0
Next
Range("B3").Select
End Sub

Also, the returned averages are being rounded to the nearest .50 even though
I have the worksheet column set to numeric with 2 decimal places.

Any help is appreciated. Thanks...

 

[joel]

I would like to see the code that puts the data into the array.
could fix the code you posted but it is better to fix the entire code.
there are either tow ways of handling an array of scores that may hav
diffferent lengths.


1) Put the scores into an array containing the exact length of round
of golf played using REDIM


Suppose the scores are in a spreadsheet like this

A B C D E F G
Bob 72 82 80 92 100 85
**** 82 92 85
Bob 72 90

Dim Scores() as variant
LastRow = Range("A" & rows.count).end(xlup).Row
for Rowcount = 2 to LastRow
ColCount = 2
Index = 0
Do while Cells(RowCount,ColCount) <> ""
Redim Scores(0 to Index)
Scores(0) = Cells(RowCount,Colcount)

ColCount = Colcount + 1
Index = Index + 1
loop
next RowCount

Now the average would be simply
Average = WorksheetFunction.Average(Scores)


I know I've simplified the problems and didn't include using the bes
scores but I was just trying to show different methods.


2) Use a multi-dimension array and add a column to the array whic
contains the number of round of golf a person has played.
Then average the number of rounds of golf a person have played usin
the following

Worksheet.function.Sum(Scores)/RoundsPlayed

Since you have in the array zeroes if less than 10 rounds werr
played.