Trendlines

Adding a trend line to a graph

Have you ever wandered, out of all of Excel's options for the type of trend line to use for your graph, which one would be best? Below, I have drawn a chart illustrating the problem. You can see that the the polynomial or the power trend lines fit the data best, but this was only discovered after adding each of the trend lines in turn, which takes time

Even after looking at all of the trend lines, you are still only making your choice by eye. A more accurate way to determine the correct trend line to use is by looking at its R² value. Doing this, however, takes even more time.

This is where my macro comes in. Once you have selected a chart, it will automatically apply a trend line to the first series, and the type chosen will depend on whichever trend line has the highest R² value. Using this macro, I have drawn a trend line for the above chart that most appropriately fits the data.

This is the output from the macro. It has chosen the power trend line and named it 'Line of best fit'.

How it works

• Get the first series on the active chart
• Add a trend line to this series, making sure that the R² value is displayed.
• Make a note of this R² value
• Loop through all of the other types of trend lines
• If the trend lines have a greater R² value than any before it, make a note of the type
• Set the trend line type to the type that scored the highest R² value
• Hide the R² value

Limitations

• This will only work on the first series of the active chart. This may cause problems if the chart has multiple series.
• It will only work on the active chart, i.e. the only way to apply it to another chart is by selecting that chart.
• You may have noticed that you can change the 'Order' of the polynomial trend line. This macro will only test order 2.
• The moving average type won't be attempted, since it is so different mathematically to all of the other trend lines (strictly speaking, it isn't really a trend line at all).
• The trend line with the best R² value may not be the same as the one that looks correct. Always double check that the trend line generated looks reasonable.
• You may need to enable macros to use this code
• You will need to know how to copy and paste code into Excel's VBE (if you don't know what that stands for, you won't be able to do it).

If you have a knowledge of VBA, you may wish to overcome these limitations by modifying the code to suit your purposes.

The code

Sub AddTrendline()
'
' AddTrendline Macro
' Macro recorded 15/05/2005 by Toby Allen
'

'

Dim i
Dim TrendNum
Dim BestSoFarR
Dim BestSoFarTN
Dim myR


    With ActiveChart.SeriesCollection(1).Trendlines.Add(xlLinear, 2, , , , , , True, "Line of best fit")
    
        BestSoFarTN = 0
        BestSoFarR = 0
        
        For i = 3 To 7
        
        If i > 5 Then TrendNum = i - 4139 Else TrendNum = i
        .Type = TrendNum
        
        myR = CDbl(Mid(.DataLabel.Text, 6, Len(.DataLabel.Text) - 5))
        
        If myR >= BestSoFarR Then
            BestSoFarR = myR
            BestSoFarTN = TrendNum
        End If
        
        'Debug.Print Mid(.DataLabel.Text, 6, Len(.DataLabel.Text) - 5)
    
        Next i
    
        'Debug.Print BestSoFarR
        'Debug.Print BestSoFarTN
    
        .Type = BestSoFarTN
        .DisplayRSquared = False
    End With
    
End Sub

Written by Toby. This work is licensed under a Creative Commons License. Privacy