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.