Hello: I am trying to calculate an average of numbers in an array for a
series of individuals. The quantity of numbers may range from none to more
than 20 and I want to average no less than 2 numbers nor more than 8 numbers.
I am using Select Case as I thought that a nested If..Then.. Else would be
cumbersome. The calculation for 8 numbers works (with the exception of a
rounding issue which will be explained further down), but when I want to
calculate averages for any fewer than 8 numbers it returns erroneous values.
It seems as if it is holding onto additional numbers so the averages are much
higher than they should be.

Following is the basic code:

Dim avg As Single

' calculate average if =2, <=8 valid numbers
avg = 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

and so forth to End Select.

holdnumbers(0-7) is also cleared (set to 0) after every calculation as so:

' reset score array
holdnumbers(0) = 0
holdnumbers(1) = 0
holdnumbers(2) = 0

Also, in calculating the average the result is returned rounded to one
decimal place, though I have the worksheet set to two decimal places. The
calculated number is set as Single. For example, a worksheet average
calculated as 87.69 is returned by the code as 87.50.

Thanks in advance for any advice you can provide.

Kind of confusing and overkill. What is the select case using

selectcase.range("a1")
???


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Hawk" wrote in message
...
Hello: I am trying to calculate an average of numbers in an array for a
series of individuals. The quantity of numbers may range from none to
more
than 20 and I want to average no less than 2 numbers nor more than 8
numbers.
I am using Select Case as I thought that a nested If..Then.. Else would be
cumbersome. The calculation for 8 numbers works (with the exception of a
rounding issue which will be explained further down), but when I want to
calculate averages for any fewer than 8 numbers it returns erroneous
values.
It seems as if it is holding onto additional numbers so the averages are
much
higher than they should be.

Following is the basic code:

Dim avg As Single

' calculate average if =2, <=8 valid numbers
avg = 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

and so forth to End Select.

holdnumbers(0-7) is also cleared (set to 0) after every calculation as so:

' reset score array
holdnumbers(0) = 0
holdnumbers(1) = 0
holdnumbers(2) = 0

Also, in calculating the average the result is returned rounded to one
decimal place, though I have the worksheet set to two decimal places. The
calculated number is set as Single. For example, a worksheet average
calculated as 87.69 is returned by the code as 87.50.

Thanks in advance for any advice you can provide.