Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Re : Excel VBA Charting of a Trendline, the Parameters do not Tally with the Calculations obtained by WorkSheetFunctions.
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Re : Excel VBA Charting of a Trendline, the Parameters do not Tall
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 " wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Re : Excel VBA Charting of a Trendline, the Parameters do not Tall
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy trendline equation to cells for use in other calculations | Excel Discussion (Misc queries) | |||
Was trendline available in Excel 2002 as a charting feature? | Charts and Charting in Excel | |||
Capturing trendline parameters | Excel Programming | |||
excel.worksheetfunctions | Excel Worksheet Functions | |||
Charting Stock Trendline | Charts and Charting in Excel |