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.
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.