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?
--
Carlee

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Carlee" <(E-Mail Removed)> wrote in message
news:3A7C19E5-0863-4966-9F16-(E-Mail Removed)...
> 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?
> --
> Carlee

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


"Don Guillett" wrote:

> > Range("HA18").Value = Application.Average(Range("C18:AG18")).Value
> use
> > Range("HA18").Value = Application.Average(Range("C18:AG18"))
>
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "Carlee" <(E-Mail Removed)> wrote in message
> news:3A7C19E5-0863-4966-9F16-(E-Mail Removed)...
> > 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?
> > --
> > Carlee
>
>

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


"Carlee" <(E-Mail Removed)> wrote in message
news:3A7C19E5-0863-4966-9F16-(E-Mail Removed)...
> 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?
> --
> Carlee

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


"Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
> 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
>
>
> "Carlee" <(E-Mail Removed)> wrote in message
> news:3A7C19E5-0863-4966-9F16-(E-Mail Removed)...
>> 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?
>> --
>> Carlee
>

The worksheet function DOES skip truly blank cells. Do you have some that
appear blank but aren't?
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Carlee" <(E-Mail Removed)> wrote in message
newsE9C4A8D-D828-49ED-A256-(E-Mail Removed)...
> THanks this worked great. Is there a way to add to this code to skip
> blank
> cells?
> --
> Carlee
>
>
> "Don Guillett" wrote:
>
>> > Range("HA18").Value = Application.Average(Range("C18:AG18")).Value
>> use
>> > Range("HA18").Value = Application.Average(Range("C18:AG18"))
>>
>>
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> (E-Mail Removed)
>> "Carlee" <(E-Mail Removed)> wrote in message
>> news:3A7C19E5-0863-4966-9F16-(E-Mail Removed)...
>> > 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?
>> > --
>> > Carlee
>>
>>