parameters of trendline in two cells ?

  • Thread starter Thread starter Double Jack
  • Start date Start date
D

Double Jack

Dear ng,
for the following data,
x y
3,19 4,4
2,91 246
2,76 4686

I show an exponential trendline in the graph: y = 4,7775E22e-1,5923E+01x
But how can I put the obtained parameters in two cells ?
Now I type them, but I would like to refer to their values.
Thanks a lot in advance for your suggestions, Marcel
 
I do not match your multiplier of 4,7775E22. Perhaps the data you
posted is rounded?

There are three options for getting the estimates in a worksheet.

1. Copy the values from the equation on the graph (you should format
them to display scientific notation with 14 decimal places.

2. David Braden has posted VBA code to extract the coefficients
directly from the chart into cells
http://groups.google.com/[email protected]

3. Use the array formula
=LINEST(LN(y),x)
What it returns as the slope will be the exponential power. What it
returns as the intercept will be LN(multiplier)

Jerry
 
thanks, to answer your question, the data is rounded indeed
I would like to implement David Braden's VBA code to extract the
coefficients. But can I please ask for some instructions how to do that: I
went to Microsoft Visual Basic, Code, then I inserted the VBA code, Debug
and after that I don't know how to continue. Would welcome some suggestions.
Thanks,
Marcel
 
This way I just successfully implemented !
But still I would prefer to use David Braden's VBA code as it is for all
chosen shapes of the trendline.
Marcel

< Use the array formula
< =LINEST(LN(y),x)
< What it returns as the slope will be the exponential power. What it
< returns as the intercept will be LN(multiplier)
 
I found the use of David's code to be pretty well described in the
leading comments of the code. It is hard to know how to help you, since
you give so little information about where you are having problems.

When I pasted the code into the macro editor, some lines improperly
wrapped, so I had to scan through and fix the wrapping, but the
locations are pretty obvious visually. You will either see black lines
in the documentation area (green comments), or red incomplete code
snippets in the executable area (among black code lines). In both types
of cases, join the offending bits to the previous line.

Two limitations that are not documented in the code are
- the code only works with charts that are on a worksheet, not on a
separate graph sheet.
- the code does not handle polynomial coefficients of +/-1 (the parser
needs to see an asterisk to separate the coefficient from the variable).

I imagine that either limitation could be removed without too much
effort, but I have not actually attempted it.

If you open a new workbook and create an unnamed single-series chart
with a trendline on the the first page, then
=TLcoef("Sheet1","Chart 1",1,1)
will extract its coefficients. You must select enough cells to hold all
the coefficients and array enter (Ctrl-Shift-Enter) the formula to see
all the coefficients. TLcoef() only extracts to the precision that the
equation is displayed on the chart, so you must take responsibility for
formatting to enough figures to be meaningful.

Note that the algorithms for LINEST() and LOGEST() were improved in
Excel 2003, so there is no reason to use this code if you have Excel 2003.

Jerry
 
Back
Top