Average row of Data using VBA

[Carlee]

Hi,
I am trying to average a row of data using VBA. The catch is that the row
may or may not contain blanks, and as such, i would like the averge
calculation to skip blank values in the calculation. This is what i have
tried, but it hasn't worked

Range("HA18").Value = Application.Average(Range("C18:AG18")).Value

Can you help me out?

 

[Don Guillett]

Range("HA18").Value = Application.Average(Range("C18:AG18")).Value
use

Range("HA18").Value = Application.Average(Range("C18:AG18"))

 

[Carlee]

THanks this worked great. Is there a way to add to this code to skip blank
cells?

 

[Rick Rothstein \(MVP - VB\)]

You could use this function...

Function vbaAverage(RangeIn As Range) As Double
Dim Total As Double
Dim Count As Long
Dim Cel As Range
For Each Cel In RangeIn
If Cel.Value <> "" Then
Total = Total + Cel.Value
Count = Count + 1
End If
Next
vbaAverage = Total / Count
End Function

and call it from within your code like this...

Range("HA18").Value = vbaAverage(Range("C18:AG18"))

Rick

 

[Rick Rothstein \(MVP - VB\)]

Maybe this slight variation that protects against a range of empty cells
would be better...

Function vbaAverage(RangeIn As Range) As Double
Dim Total As Double
Dim Count As Long
Dim Cel As Range
For Each Cel In RangeIn
If Cel.Value <> "" Then
Total = Total + Cel.Value
Count = Count + 1
End If
Next
If Count > 0 Then vbaAverage = Total / Count
End Function

Rick

 

[Don Guillett]

The worksheet function DOES skip truly blank cells. Do you have some that
appear blank but aren't?