add horizontal average line across excel chart

[helenkim]

hi,
I am trying to add a horizontal average line across a 2Dcolumn chart but i don't know how.

i have the following labels on my chart...

X-axis: weekdays (M-F)
Y-axis: values associated with each weekday.
M:5
T: 8
W: 9
Th:2
F: 8

the average is 6.4

I would like to have a straight horizontal line at 6.4 going across the column chart to indicate what the average is. any idea how?

this is prob really simple but i can't seem to get around it. any help would be greatly appreciated.

thank you

 

[Andrew Poulsom]

Look here:

http://peltiertech.com/Excel/Charts/DummySeries.html#AddLine

 

[MickG]

Right click the data series on the chart, Select "Add Trendline", "Linear".
You should now have an horizontal line at 6.4
Mick

 

[helenkim]

Thanks all!

But with the treadline, it doesn't start from the "0" axis point. anyway to get the trendline to start all the way from the left axis?

 

[Andrew Poulsom]

Try the link I posted. The trendline won't always be horizontal.

 

[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 VBA.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