Trendlines with dates

[sts023]

Hi Guys....

I have some code built by butchering some sample code found on your site.

It was researched to get over the immensely irritating problem of being unable to get the formula from a trendline when executing chart creation VBA normally, but the formula becomes available when stepping through it.

From what I can gather, the normal solution to this problem is to use either the LinEst or Slope function to generate the formula from the values.

However, I seem to have a weird problem (which I suspect is related to the data involving a date) - the LinEst and Slope functions yield identical but significantly different formulae from the visible one (and by inspection, the visible one is correct)
On the Worksheet, I have the following data in cells A1:B5.
The date cells in col A are formatted as "dd/mm/yyyy".
The value cells in col B are formatted as numeric, with 0 decimal places.
[TABLE="class: grid, width: 100, align: left"]
<tbody>[TR]
[TD]15/02/2015[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]07/03/2015[/TD]
[TD]55[/TD]
[/TR]
[TR]
[TD]27/03/2015[/TD]
[TD]83[/TD]
[/TR]
[TR]
[TD]16/04/2015[/TD]
[TD]27[/TD]
[/TR]
[TR]
[TD]06/05/2015[/TD]
[TD]13[/TD]
[/TR]
</tbody>[/TABLE]

The visible equation on the chart is
"y=0.31x-13005",
but the LinEst and Slope functions both generate
"y = 6.2x + 24.2"
The code follows

Option Explicit
Sub Test()
Dim objChart            As Excel.ChartObject
Dim strDataRange        As String
Dim rngText             As Excel.Range
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  Application.EnableEvents = False
  For Each objChart In ActiveSheet.ChartObjects
    objChart.Delete
  Next objChart
  strDataRange = "A1:B5"
  Set rngText = ActiveSheet.Range("G1:K3")
  rngText.Delete
  Call TestGraph("TEST FORMULAE", strDataRange)
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True
  Application.EnableEvents = True
End Sub
Sub TestGraph(strTitle As String, _
              strData As String)
'***** this sub-routine adds a graph to the current sheet
'On Error GoTo ErrMsg 'this error handling isn't normally here, I just added it incase something odd was happening
Dim intPARight              As Integer
Dim intPATop                As Integer
Dim rngToCover              As Range
Dim strArg1                 As String
Dim strArg2                 As String
Dim strArg3                 As String
Dim strArg4                 As String
Dim strTP                   As String
Dim varTransposed           As Variant
Dim varValues               As Variant
Dim varWSFLinEst            As Variant
Dim varWSFSlope             As Variant
Dim varWSFIntercept         As Variant
'*
'** Set the size and position of the Chart.
'*
  Set rngToCover = ActiveSheet.Range("D6:K17")
  With ActiveSheet.ChartObjects.Add(Left:=rngToCover.Left, _
                                    Width:=rngToCover.Width, _
                                    Top:=rngToCover.Top, _
                                    Height:=rngToCover.Height)
    With .Chart
' Get data to use later when positioning the formula.
      intPATop = .PlotArea.Top
      intPARight = .PlotArea.Left + .PlotArea.Width
      .HasTitle = True
'Set Title
      .ChartTitle.Text = strTitle
'Set source
      .SetSourceData Source:=ActiveSheet.Range(strData)
'Set Chart type
      .ChartType = xlLine
'Format the series
      With .SeriesCollection(1)
        .Name = "Raw data"
        .trendlines.Add
        With .trendlines(1)
          .DisplayEquation = True
          .DataLabel.Left = intPARight
          .DataLabel.Top = intPATop
        End With
'Recover the values into a variant
        varValues = .Values
      End With
    End With
  End With
'Now get the trend equation by other means.
  strTP = "Transpose(Row(A1:A" & _
          UBound(varValues) & _
          "))"
  varTransposed = Evaluate(strTP)
     
  With Application.WorksheetFunction
    varWSFLinEst = .LinEst(varValues, _
                           varTransposed)
    varWSFSlope = .Slope(varValues, _
                         varTransposed)
    varWSFIntercept = .Intercept(varValues, _
                                 varTransposed)
  End With
  strArg1 = Format(varWSFLinEst(1), "+ 0.####;- 0.####")
  strArg2 = Format(varWSFLinEst(2), "+ 0.####;- 0.####")
  strArg3 = Format(varWSFSlope, "+ 0.####;- 0.####")
  strArg4 = Format(varWSFIntercept, "+ 0.####;- 0.####")
  With ActiveSheet
    .Range("G1") = "LinEst: " & _
                   strArg1 & "x " & strArg2
    .Range("G2") = "Slope: " & _
                   strArg3 & "x " & strArg4
  End With
End Sub

Can anyone help me to get the correct equation?

 

[joeu2004]

It was researched to get over the immensely irritating problem of being unable to get the formula from a trendline when executing chart creation VBA normally, but the formula becomes available when stepping through it.

I have had similiar experience when I use a Line chart as opposed to an XY-Scatter chart.

However, I seem to have a weird problem (which I suspect is related to the data involving a date) - the LinEst and Slope functions yield identical but significantly different formulae from the visible one (and by inspection, the visible one is correct)
[....]
The visible equation on the chart is "y=0.31x-13005",
but the LinEst and Slope functions both generate "y = 6.2x + 24.2"
The code follows

With .Chart
       [....]
       .ChartType = xlLine
       [....]
  End With

Can anyone help me to get the correct equation?

As Ron Rosenfeld points out in another discussion (click here), trendlines are not reliable in Line charts -- in fact, not in any chart other than XY-Scatter charts. See the "Note" in KB 211967 (click here).

(The rest of the KB is misleading, IMHO. It describes a user error, not an Excel error. But ironically, it might apply to you.)

Bottom line: Do not use Line charts to create and display trendlines. Use XY-Scatter charts instead.

In Excel 2003, the slope in the Line chart trendline formula seems to be a monthly rate of change, whereas slope returned by LinEst and Slope is a daily rate of change.

Technically, the latter is correct because the units of the X-axis are days. This is because dates are integers, namely days since 31-Dec-1899 if the dates are created in Excel.

FYI, in Excel 2010 and later (perhaps even in Excel 2007), the slope in the Line chart trendline formula seems to no longer be a monthly rate of change. But it still differs from the slope returned by LinEst and Slope. I have not analyzed it to see if it is correct. But note that KB 211967 indicates that it still applies to Excel 2010.

PS.... My comments are really based on the Excel functions LINEST and SLOPE. When you write LinEst and Slope, I presume you mean the Worksheet.Function methods. And I presume that they behave the same as the Excel functions.

 

[sts023]

Hi joeu2004....

Being inexperienced in chart production, I had noticed but avoided the XY Scatter charts, 'cos obviously, I didn't want a load of confusing dots looking like an explosion in a gravel factory - I wanted a nice solid line.

I am now doubly delirious, 'cos not only can I get at the equation by using XY Scatter, I also discovered xlXYScatterSmoothNoMarkers, so my graph will look quite sophisticated (it's for visual impact, rather than absolute accuracy, 'cos my boss can't understand numbers, but he LOVES shapes!).

All I need now is some filler paste to smooth out the cavities in the walls, caused by the repetitive banging of my head for several days....

 

[joeu2004]

PS....

The date cells in col A are formatted as "dd/mm/yyyy".
The value cells in col B are formatted as numeric, with 0 decimal places.
[TABLE="class: grid, width: 100, align: left"]
<TBODY>[TR]
[TD]15/02/2015
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]07/03/2015
[/TD]
[TD]55
[/TD]
[/TR]
[TR]
[TD]27/03/2015
[/TD]
[TD]83
[/TD]
[/TR]
[TR]
[TD]16/04/2015
[/TD]
[TD]27
[/TD]
[/TR]
[TR]
[TD]06/05/2015
[/TD]
[TD]13
[/TD]
[/TR]
</TBODY>[/TABLE]
The visible equation on the chart is
"y=0.31x-13005",
but the LinEst and Slope functions both generate
"y = 6.2x + 24.2"

Bottom line: Do not use Line charts to create and display trendlines. Use XY-Scatter charts instead.

In Excel 2003, the slope in the Line chart trendline formula seems to be a monthly rate of change, whereas slope returned by LinEst and Slope is a daily rate of change.

The important take-away is: Line chart trendlines are unreliable, according to Microsoft. Use XY-Scatter charts instead. Or use the Excel functions LINEST or SLOPE and INTERCEPT.

But the slope and intercept values that I get for the data that you posted are very different from the slope and intercept values that you posted.

So if you need a better explanation, please upload an example Excel file to a file-sharing website and post the public/share URL in a response here.

Also, please specify the version of Excel that you are using.

PS.... Apparently, the monthly v. daily rate of change behavior in Line charts depends on the data (dates?). With the data that you posted, the Line chart trendline formula and Excel and WorksheetFunction LINEST, SLOPE and INTERCEPT functions all return about the same values in both XL2003 and XL2010.

 

[sts023]

joeu2004....

Thanks for the advice - I got the message about the unreliability of trend lines in anything but XY Scatter charts.

The spreadsheet used in the post was a dramatically slimmed down extract from my real code, which for commercial reasons I cannot post. The code I posted was just to show the problem, and I then butchered it to remove all the unnecessary code and verify that your solution would work for me.

I only wanted to get at the trendline's equation in order to predict at what date a specific value will be achieved.

With your kind help I can now continue, and my Boss is fairly anxious for me to get on with the development work.

In my original post I believe I gave all of the pertinent info, except that I'm using Excel from Microsoft Office Professional Plus 2010, running under Windows 7 Professional with SP1.

If I get time later I'll reassemble the original post's spreadsheet, and stick it on Dropbox, but it will be a day or so, 'cos this issue has played havoc with my schedules.

Thanks again for all your help....

 

[joeu2004]

With your kind help I can now continue, and my Boss is fairly anxious for me to get on with the development work.
[....]
If I get time later I'll reassemble the original post's spreadsheet, and stick it on Dropbox, but it will be a day or so, 'cos this issue has played havoc with my schedules.

No need. If you consider your questions answered, we're done. Glad it helped.

 

[sts023]

Oh Bums!

I spoke too soon.

Having changed the chart types to XY Scatter, I'm still not getting the equation from the trendlines (in reality I have a linear and polynomial trend line on the chart).

I have a distant memory that sometimes the order in which you create the chart elements can affect the way in which other elements are produced, so I'll do some more digging....