Re : Excel VBA Charting of a Trendline, the Parameters do not Tally with the Calculations obtained b

T

tkt_tang

Re : Excel VBA Charting of a Trendline, the Parameters do not Tally
with the Calculations obtained by WorkSheetFunctions.

1. Enter an Excel Chart-Sheet and plot a Line Chart complete with a
Trendline (via data values as listed on an associated WorkSheet).

2. The xlCategory-axis is tick-labelled with a range of Dates such as,

1981-Apr 1987-Oct 1987-Nov 1988-Jun 1989-Dec 1990-Dec 1991-Sep 1992-
Nov 1994-Mar 1995-Jun 1996-Apr 1996-Oct 1998-Jan 1999-Jun

3. Note that the Series of Dates is entered in a reference range named
"DateGauge" (of the WorkSheet).

4. The corresponding xlVaue-axis is tick-labelled with a range of
Values such as,

20.63 , 22.10 , 22.20 , 22.00, 22.20 , 22.20 , 22.20 ,
22.20 , 22.20 , 22.60 , 23.10 , 22.70 , 21.90 , 18.60

5. Note that the Series of Dates is entered in a reference range named
"WallThickField" (of the WorkSheet).

6. The resultant Trendline equations are given (by virtue of VBA code
statements) as follows,

With .SeriesCollection(1).Trendlines(1)
.DisplayEquation = True
.DisplayRSquared = True
End With

Produces .......... ,

y = -0.000572x + 22.549510
R2 = 0.000957 (Note that "R2" is to be read "R superscript 2")

7. However, the Trendline does not appear to Intercept the y-axis at
22.549510 ; the point of Interception (as shown on the Chart) is
rather approaching (from below) 22.0.

8. And then, there's an attempt to verify the Trendline equations (as
given above) by the following WorkSheetFunction Calculations,

9. Application.Slope(Range("WallThickField"), Range("DateGauge"))
gives,
Best-Fit Rate = -1.66912716320947E-05, which is rather a far cry from
-0.000572x.

10. Application.Intercept(Range("WallThickField, Range("DateGauge"))
gives,
Intercept at Y-Axis = 22.478726514638, which is not quite correct
(according to the depiction on Chart).

11. Rsquared = Application.RSq(Range("WallThickField"),
Range("DateGauge")) gives, RSQ = 7.544141E-04, which is obviously
deviating from 0.000957.

12. Please share your comments. Regards.
 
J

Jerry W. Lewis

A trendline in a "Line Chart" is usually meaningless. Use an "XY (Scatter)"
chart instead.

"Line" chart is a misleading name which, together with its higher place in
the chart wizard list of chart types, invites misunderstanding and misuse. A
"Line" chart is a chart where the x-axis is assumed to be category labels
instead of numeric values. Why Excel even offers to fit a trend to category
labels (which may not even have a well defined order) is a mystery. When it
does fit a trendline, it assumes that the plotted x-values are 1,2,...
completely ignoring the numeric values (if any) of the category labels.

Excel dates are stored as the number of days since 1900. Therefore the
numeric value of 1Apr1981 is 29677, not 1. The numeric value of 1Oct1987 is
32051, not 2, ... With the "Line" chart and the worksheet function being
given completely different x-values to work with, they should (and do) get
different answers. Moreover, these dates are not equally spaced, so that the
"Line" chart coefficients are not even a transformation of the intended
results.

Jerry
 
T

tkt_tang

Mr. Jerry W. Lewis,

Appreciate your response very much indeed.

The situation has now improved after applying XY-Chart (namely,
Scatter Plot).

Once again, Thank You very much indeed for giving such Clarification.

Regards.
 
Top