|
||||||||||||||||||||
|
|||||||||||||||||||||
|
#1
05-03-2005, 09:06 AM
|
|||
|
|||
|
Locating Trendline Equation Factors in Cells
When adding a trendline on a chart, one option is to add the trendline
equation to the chart. Is there a way to also populate off-chart cells with the various factors of the equation? For example: the equation on a chart is: y = 5.1460E+05Ln(x) - 2.5051E+06. The 5.1460E+05Ln(x) value would be found in cell A1, and the –2.5051E+06 value in cell A2. As values are added to the data matrix and the equation changes, the cell values would change accordingly. 
|
|
#2
05-03-2005, 09:06 AM
|
|||
|
|||
|
Re: Locating Trendline Equation Factors in Cells
Hi Phil,
Have a look at Bernard Liengme's examples of using worksheet formula to determine equation elements. http://www.stfx.ca/people/bliengme/ExcelTips Cheers Andy Phil Hageman wrote: > When adding a trendline on a chart, one option is to add the trendline > equation to the chart. Is there a way to also populate off-chart cells with > the various factors of the equation? > > For example: the equation on a chart is: y = 5.1460E+05Ln(x) - 2.5051E+06. > The 5.1460E+05Ln(x) value would be found in cell A1, and the –2.5051E+06 > value in cell A2. As values are added to the data matrix and the equation > changes, the cell values would change accordingly. > -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info
|
|
#3
05-03-2005, 10:06 AM
|
|||
|
|||
|
Re: Locating Trendline Equation Factors in Cells
Alternately, David Braden has posted VBA code to extract the
coefficients (rounded per display - therefore format the chart equation as Scientific with 14 decimal places) directly from the chart into cells http://groups.google.com/groups?selm....microsoft.com The choice would depend on what version of Excel you are running. In versions prior to 2003, LINEST/LOGEST/TREND/GROWTH used numerically inferior algorithms that could give wrong answers in numerically challenging situations, while the chart trendline algorithms have always been excellent. If you have 2003, download the most recent hotfixes and be suspicious if any coefficients are estimated to be exactly zero. Jerry Andy Pope wrote: > Hi Phil, > > Have a look at Bernard Liengme's examples of using worksheet formula to > determine equation elements. > http://www.stfx.ca/people/bliengme/ExcelTips > > Cheers > Andy > > Phil Hageman wrote: > >> When adding a trendline on a chart, one option is to add the trendline >> equation to the chart. Is there a way to also populate off-chart >> cells with the various factors of the equation? >> >> For example: the equation on a chart is: y = 5.1460E+05Ln(x) - >> 2.5051E+06. The 5.1460E+05Ln(x) value would be found in cell A1, and >> the –2.5051E+06 value in cell A2. As values are added to the data >> matrix and the equation changes, the cell values would change >> accordingly.
|
|
#4
05-03-2005, 10:06 AM
|
|||
|
|||
|
Re: Locating Trendline Equation Factors in Cells
Andy,
Thanks for your reply. I looked at Bernard's examples and don't exactly understand the big picture... When Excel calculates a logarithmic trendline (or any other trendline for that matter), it must hold the equation factors somewhere. That's what I'm after. In the case below (plot data: x=dates, y = values), the resulting two trendline equation factors (values) change slightly each period, and that value is what is needed. Or, is a full dress approach such as Bernard's the only path? "Andy Pope" wrote: > Hi Phil, > > Have a look at Bernard Liengme's examples of using worksheet formula to > determine equation elements. > http://www.stfx.ca/people/bliengme/ExcelTips > > Cheers > Andy > > Phil Hageman wrote: > > When adding a trendline on a chart, one option is to add the trendline > > equation to the chart. Is there a way to also populate off-chart cells with > > the various factors of the equation? > > > > For example: the equation on a chart is: y = 5.1460E+05Ln(x) - 2.5051E+06. > > The 5.1460E+05Ln(x) value would be found in cell A1, and the –2.5051E+06 > > value in cell A2. As values are added to the data matrix and the equation > > changes, the cell values would change accordingly. > > > > -- > > Andy Pope, Microsoft MVP - Excel > http://www.andypope.info >
|
|
#5
05-03-2005, 10:06 AM
|
|||
|
|||
|
Re: Locating Trendline Equation Factors in Cells
Hi,
You could try David's code as suggested by Jerry to extract the info from the data label. activechart.SeriesCollection(1).trendlines(1).datalabel.text Cheers Andy Phil Hageman wrote: > Andy, > Thanks for your reply. I looked at Bernard's examples and don't exactly > understand the big picture... When Excel calculates a logarithmic trendline > (or any other trendline for that matter), it must hold the equation factors > somewhere. That's what I'm after. In the case below (plot data: x=dates, y > = values), the resulting two trendline equation factors (values) change > slightly each period, and that value is what is needed. Or, is a full dress > approach such as Bernard's the only path? > > "Andy Pope" wrote: > > >>Hi Phil, >> >>Have a look at Bernard Liengme's examples of using worksheet formula to >>determine equation elements. >>http://www.stfx.ca/people/bliengme/ExcelTips >> >>Cheers >>Andy >> >>Phil Hageman wrote: >> >>>When adding a trendline on a chart, one option is to add the trendline >>>equation to the chart. Is there a way to also populate off-chart cells with >>>the various factors of the equation? >>> >>>For example: the equation on a chart is: y = 5.1460E+05Ln(x) - 2.5051E+06. >>>The 5.1460E+05Ln(x) value would be found in cell A1, and the –2.5051E+06 >>>value in cell A2. As values are added to the data matrix and the equation >>>changes, the cell values would change accordingly. >>> >> >>-- >> >>Andy Pope, Microsoft MVP - Excel >>http://www.andypope.info >> -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info
|
|
#6
05-03-2005, 11:06 AM
|
|||
|
|||
|
Re: Locating Trendline Equation Factors in Cells
See Trendline coefficients
http://www.tushar-mehta.com/excel/ti...efficients.htm Note that the page works with IE or Firefox. Apparently, it contains=20 formatting that is incompatible with Navigator. --=20 Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article <0B74E795-E436-47A7-997F-7C15021D596D@microsoft.com>,=20 PhilHageman@discussions.microsoft.com says... > Andy, > Thanks for your reply. I looked at Bernard's examples and don't exactly= =20 > understand the big picture... When Excel calculates a logarithmic trendl= ine=20 > (or any other trendline for that matter), it must hold the equation facto= rs=20 > somewhere. That's what I'm after. In the case below (plot data: x=3Ddat= es, y=20 > =3D values), the resulting two trendline equation factors (values) change= =20 > slightly each period, and that value is what is needed. Or, is a full dr= ess=20 > approach such as Bernard's the only path? >=20 > "Andy Pope" wrote: >=20 > > Hi Phil, > >=20 > > Have a look at Bernard Liengme's examples of using worksheet formula to= =20 > > determine equation elements. > > http://www.stfx.ca/people/bliengme/ExcelTips > >=20 > > Cheers > > Andy > >=20 > > Phil Hageman wrote: > > > When adding a trendline on a chart, one option is to add the trendlin= e=20 > > > equation to the chart. Is there a way to also populate off-chart cel= ls with=20 > > > the various factors of the equation? > > >=20 > > > For example: the equation on a chart is: y =3D 5.1460E+05Ln(x) - 2.50= 51E+06. =20 > > > The 5.1460E+05Ln(x) value would be found in cell A1, and the =E2=A4= =3D3F2.5051E+06=20 > > > value in cell A2. As values are added to the data matrix and the equ= ation=20 > > > changes, the cell values would change accordingly. > > >=20 > >=20 > > --=20 > >=20 > > Andy Pope, Microsoft MVP - Excel > > http://www.andypope.info > >=20 >=20
|
 |
| Bookmarks |
New topics in Excel Charting
|
|
|
| Currently Active Users Viewing This Thread: 1 (0 members and 1 guests) | |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
