Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
4  
Make one yourself from an extra column in your spreadsheet;-) –  trolle3000 May 26 '10 at 19:29
    
I can... but I need this occasionally for different columns. Maybe there is any "two-click" shortcuts... –  stim May 26 '10 at 20:59
1  
There is no existing short-cut. You will have to write your own macro. –  systemovich Jul 13 '10 at 13:47

3 Answers 3

up vote 4 down vote accepted

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.

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer
1  
I need average - exact value, not the upward or downward trend. I can't see value on trend line. –  stim May 26 '10 at 21:04
    
What do you mean by "you can't see value on trend line"? If you want to see the equation of the trendline, that's an option when you customize the trendline. If you mean you can't see the use of a trendline, it's so that you can see the best-fit line for the dataset. –  nhinkle Jul 21 '10 at 5:51
1  
No. Trendline just tell us the trend. Average line is a simple horizontal line with average value. –  stim Mar 21 '12 at 18:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.