VBA Calculation with Variables

H

Hawk

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.
 
D

Don Guillett

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

selectcase.range("a1")
???
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top