VBA Excel Create average and count function on cell range

[dprayner]

Good afternoon people.

I am trying to create a function which selects a range (ie. a1:a10) which has integers 1-100. Some of the cells contain 0 and I want to total the cell amounts that are > 0 and count how many cells do not contain 0. I am trying to create an average for example 10 cells 5 have 0s and 5 do not. The values in the 5 cells are 10, 20, 30, 40, and 50. The number of cells is 5 and the average of the 5 cells is: 30. Any suggestions? I need this to work for multiple columns A,B,C,D, etc. this is where I am getting hung up. Thank you, DAVE

 

[SkipVought]

Hi,

Can be done right on the sheet using
[tt]
=countif(YourRange,">0")
[/tt]
or use the function in your VBA code.

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[SkipVought]

... and SUMIF.

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[dprayner]

Hi guys. This is what I came up with so far:

Function Ave_Merlin(rSumRange)
Dim rCell As Range
Dim result, count, subtotal As Variant
For Each rCell In rSumRange
If rCell.Value > 0 Then
count = count + 1
subtotal = subtotal + rCell.Value
End If
Next rCell
result = subtotal \ count
MsgBox "Subtotal= " & subtotal
MsgBox "count= " & count
MsgBox "Result= " & result
Ave_Merlin = result
End Function

The result displays a whole number (ie. 15) I formatted the cell for number with 1 decimal place and if the number is 15.5 the MsgBox for result displays the 15 and th cell displays 15.0. I can't get the 15.5 to come out on the spreadsheet of in the MsgBox. It doesn't round up either.
What am I doing wrong with the declarations and/or cell formatting? DAVE

 

[SkipVought]

result = subtotal [red][b]/[/b][/red] count

to get a floating pt value. otherwise (\) you ONLY get the integer portion.

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[dprayner]

Thank you Skip. I knew I was overlooking something simple. DAVE