karolina1406
Board Regular
- Joined
- Apr 18, 2016
- Messages
- 110
- Office Version
- 365
- Platform
- Windows
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
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