Into the corresponding Excel formula = 3*A9A3 + 2.5*A9A2 - 5*A9 - 11 Two dialog boxes direct you to, first, select the destination cell for the formula, and second, select the cell for the independent variable The utility converts a trendline equation such as y = 3x3 + 2.5x2 - 5x -11 To use the utility, you first must select a Trendline equation in a chart. The procedure is an Auto Open macro when you open the document, the procedure installs a new menu command, Copy Trendline to Cell., in the Tools menu of the Chart menu bar (see Figure 13-18), then hides itself.įigure 13-18. NumberFormat = TLNumberFormatįigure 13-17. ChartObjects(ChartObjectName).Activate End IfĪctiveChart.SeriesCollection(SeriesNum) _ 'RETURN TO TRENDLINE TEXT TO RESTORE ORIGINAL NUMBER FORMAT If ChartSheetName "" Then Charts(ChartSheetName).Activate Charts(ChartSheetName).ChartArea.Select ElseĪctiveSheet. "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:= _
![excel trendline equation in cell excel trendline equation in cell](https://media.cheggcdn.com/media/f7a/f7a112cd-4a19-4f11-a245-8e53729cddac/phprLHmE0.png)
![excel trendline equation in cell excel trendline equation in cell](https://qrc.depaul.edu/qr_tools/excel2007_files/image002.gif)
'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.
![excel trendline equation in cell excel trendline equation in cell](https://cdn.ablebits.com/_img-blog/trendline-types/excel-trendline-types.png)
![excel trendline equation in cell excel trendline equation in cell](http://websites.umich.edu/~elements/05chap/html/excel_tutorial/Excelfit_files/image001.jpg)
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.