"Select cell for independent variable x.", Title:= _ "COPY TRENDLINE TO CELL - STEP 2 OF 2 ", Type:=8) X = XAddress.Address X = Application.Substitute^, "$","") TLText = Application.Substitute(TLText, "x", X) Range(Y).Formula = TLText CancelWasPressed: On Error GoTo 0 Set XAddress = Application.lnputBox(prompt:= _ 'INPUT REFERENCES FOR FORMULA AND X On Error GoTo CancelWasPressed Set YAddress = Application.lnputBox(prompt:= _ "Select destination cell for formula.", Title:= _ "COPY TRENDLINE TO CELL - STEP 1 OF 2 ", Type:=8) Y = YAddress. TLText = Application.Substitute(TLText, "e", "*EXP(") TLText = Application.Substitute (TLText, "x", "*x") End Select TLText = Application.Substitute(TLText, "x", "*xA") Case 5 'Exponential TLText = Application.Substitute(TLText, "x", "*x") Case-4133 'Logarithmic TLText = Application.Substitute(TLText, "L", "*L") Case 3 'Polynomial TLText = Application.Substitute(TLText, "x", "*xA") TLText = Application.Substitute(TLText, "xA ", "x") Case 4 'Power Trendlines (TrendlineNum).Type Case-4132 'Linear 'CONVERT DIFFERENT TYPES OF TRENDLINE EQUATION Select Case ActiveChart.SeriesCollection(SeriesNum) _ If Not (lsError(Application.Find("R", TLText))) Then pointer = Application.FindfR", TLText) TLText = pointer = Application.Find("=", TLText) 'CONVERT TRENDLINE TEXT TO AN EXCEL FORMULA 'CHANGE NUMBER FORMAT TEMPORARILY TO GET SUFFICIENT PRECISION TLNumberFormat = Selection.NumberFormat Selection.NumberFormat = "0.0000000000E+00" On Error GoTo BadSelection 'Selection.Name e.g., "Text S3T1" If Selection.Name Like "Text S*T*" Then pointer = Application.Find('T", Selection.Name, 3) SeriesNum = Val(Mid(Selection.Name, 7, pointer - 7)) TrendlineNum = Val(Mid(Selection.Name, pointer +1,3)) ElseīadSelection: MsgBox "You must select a Trendline label." Exit Sub End If On Error GoTo 0 'REMEMBER LOCATION OF CHART If TypeName(ActiveSheet) = "Chart" Then ChartSheetName = ActiveSheet.Name Else pointer = Application.Find("Chart", ActiveChart.Name) ChartObjectName = Mid(ActiveChart.Name, pointer, 100) End If 'Tranfers Trendline text to cell as formula. The utility TrendlineToCell provided on the CD-ROM converts the Trendline equation to an Excel formula and transfers the formula to a selected cell on a worksheet. The disadvantage of Trendline is that the trendline equation is merely a caption in the chart to use it in the worksheet, the coefficients must be transferred manually by typing, or copying and pasting. Trendline provides a limited gallery of mathematical fitting functions, including regular polynomials up to order six.
Scientists and engineers often use Excel's Trendline feature to obtain a least-squares fit to data in a chart.
0 kommentar(er)
