Exponential trendlines
John Tomaszewski
Hi all,
I have a goal in mind that I can't seem to accomplish in Excel
(Office95 or 97 versions) and would appreciate any assistance.
I have a series of data points that I'm fitting with an
exponential trendline. This works fine and I can chart them with out any
problem and if I choose, plot the line equation (y=ce^-xb) on the chart.
My problem is, for these series of 40-60 values I want to extract the
exponential value "b" into a cell to use for further calculations, and so
far the only way I can see to do it is to read it off the graph and
manually type it into a cell, but with many data values and overlap on the
chart, this isn't very efficient. Does anyone know how to generate the
line equation or the variables into cells, independant from the chart?
Any assistance would be greatly appreciated.
Sincerely,
John T.
********************************************************************
** ** ** ** * ** DEPARTMENT OF CHEMISTRY **
** ** ** ** * ** tom...@protein.chem.washington.edu **
** ** ** ** * ** (206) 616-2993 **
** ***** ***** JOHN TOMASZEWSKI **
********************************************************************
Bernard Liengme
Lets assume the X values are in A2:A46 and the Y values are in B2:B46. Some
where to the far right - lets say in column Q add in Q2 the formula =LN(B2)
and copy this down to Q46.
In the cell where you want the value of b, enter =LINEST(Q2:Q46, A2:A46) and
Excel will return the value.
If you first select a horizontal range of 2 cells, type the formula about,
press CTRL+SHIFT+ENTER, then the first cell will contain b and the seconf
LN(c).
If y = cEXP(bx)
Then LN(y) = LN(c) + bx ----> so we can use a linear plot with slope b and
intercept c
Hope this helps.
Dana De
John. You can extract the equation that excel calculates. The only way I
know of is to use Visual Basic.
However, for most trendlines, it is returned as text with the power raised
to a non mathematical
notation as x6 instead of x^6.
You will have to add your own routine to extract the numbers to fit your
situation. But as least, this will get you started.
For now, place an embedded chart on your sheet and add a trendline to the
first series.
Leave cells A1 and A2 blank because this is where this routine places the
equations. (Adjust these as necessary.) Adjust the accuracy of the
returned number with NumberFormat.
Of course, you will have to adjust most of this to fit your needs.
Here is the macro. If you have any problems extracting the numbers to fit
your situation, than
please let me know.
This took me a long time to figure out when I was in graduate school, so
I'm pretty proud of this one.
Sub TrendLabel()
' Select your chart here.
ActiveSheet.ChartObjects(1).Activate
' Select your trendline here.
With ActiveChart.SeriesCollection(1).Trendlines(1)
' The DataLabel is 1 object,
' so work with only 1 equation at a time.
.DisplayEquation = True
.DisplayRSquared = False
' Give it your own format
.DataLabel.NumberFormat = "#,##0.0000000"
Worksheets("sheet1").Range("A1").Value = .DataLabel.Text
' Now work with the R Squared
.DisplayEquation = False
.DisplayRSquared = True
.DataLabel.NumberFormat = "#,##0.0000000"
Worksheets("sheet1").Range("A2").Value = .DataLabel.Text
End With
End Sub
John Tomaszewski wrote in message ...
>Hi all,
> I have a goal in mind that I can't seem to accomplish in Excel
>(Office95 or 97 versions) and would appreciate any assistance.
> I have a series of data points that I'm fitting with an
>exponential trendline. This works fine and I can chart them with out any
>problem and if I choose, plot the line equation (y=ce^-xb) on the chart.
>My problem is, for these series of 40-60 values I want to extract the