how to add an average line to the graph in excel 2007 or 2010

Question

In OpenOffice I'm getting trend-line and average-line properties by RightClick. But in Excel 2007 I see only trend-line. Same with Excel 2010. How do you make an average-line in Excel?

Answer 1

I only have Excel 2003, but I think it should be the same.

I created a line chart then right-clicked on one of the data lines. The first item on the right-click menu was "Format Data Series..." On that right-click menu is "Add Trendline"

This will give you a bunch of options for the type of trendline. From the Options tab, you can have it forecast forward and backward as well.

Answer 2

BTW, I've got desired macros:

Sub averageline()

' Adds line of average value for the selected series

Dim s As Series, v, m As Double, v1, i As Long
On Error GoTo err_selection
Set s = Selection
On Error GoTo 0
v = s.Values
m = WorksheetFunction.Average(v)
v1 = v
For i = LBound(v) To UBound(v)
    v1(i) = m
Next
With ActiveChart.SeriesCollection.NewSeries
    .XValues = s.XValues
    .Values = v1
    .Name = "Average " & s.Name
    .AxisGroup = s.AxisGroup
    .MarkerStyle = xlNone
    .Border.Color = s.Border.Color
End With

Exit Sub

err_selection:
    MsgBox "Selection don't represent series on the chart", vbCritical
    Exit Sub
End Sub

After adding it, you'll be able to simply select any row on your graph, then alt+f8, select macros and enter.

Answer 3

Assuming an already existing XY Scatter chart:

1. Add a column to your spreadsheet where the chart data resides. In this column, for every row enter the average. You will have calculated this average beforehand using AVERAGE() or SUM(data range)/(number of rows). You should now have a column that has the same value for every cell. You can title this Average, or something relevant to your needs.

2. In your chart, add another data series. This new series will be of the same type as your existing data line. Everything about this new series will be the same as the existing data series, except you will now use this new column as the range instead of the range you used for the data series (you'll probably also want to use a different title). Most likely you are changing the range used for the Series Y values (assuming a horizontal line showing the average). The point I'm trying to convey here is that the only difference between your data line and your average line will be that the average line uses the average column you just created instead of values used to create the original chart. This will draw a straight horizontal (no slope) average line.

3. Format the line to your tastes.