amending macro

[karolina1406]

hi,

i used fab macro script to plot average on the chart. However, i noticed that the macro calculates an average taking into account elements with 0 values. Does anyone know how to amend below to exclude from calculations elements with zero value?

​Sub AverageLine()'Update 20130907
Dim ser As Series
Dim arr As Variant
Dim total As Double
Dim outArr As Variant
If <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-top-style: initial; border-right-style: initial; border-bottom-style: dotted; border-left-style: initial; border-top-color: initial; border-right-color: initial; border-bottom-color: rgb(0, 0, 0); border-left-color: initial; border-image: initial; cursor: help;">VBA</acronym>.TypeName(Application.Selection) <> "Series" Then Exit Sub
Set ser = Application.Selection
arr = ser.Values
total = Application.WorksheetFunction.Average(arr)
ReDim outArr(LBound(arr) To UBound(arr))
For i = LBound(outArr) To UBound(outArr)
outArr(i) = total
Next
With ActiveChart.SeriesCollection.NewSeries
.XValues = ser.XValues
.Values = outArr
.Name = "Average " & ser.Name
.AxisGroup = ser.AxisGroup
.MarkerStyle = xlNone
.Border.Color = ser.Border.Color
.ChartType = xlLine
.Format.Line.ForeColor.RGB = RGB(255, 0, 0)
.Format.Line.DashStyle = msoLineDash
End With
End Sub

 

[Rob65]

I would have thought you would change this

total = Application.WorksheetFunction.Average(arr)

to

total = Application.WorksheetFunction.Averageifs(arr,arr,">0")

 

[karolina1406]

I would have thought you would change this

total = Application.WorksheetFunction.Average(arr)

to

total = Application.WorksheetFunction.Averageifs(arr,arr,">0")

thank you for this... however, I get some error which says" Run-time error '424': object required....