View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.charting
[email protected] jonathan.laberge@gmail.com is offline
external usenet poster
 
Posts: 1
Default Logarithmic Trendline

Ok, I apologize if this is a stupid question, but I'm really confused
about a result from Excel's trendline options.

My data is below:

X Y
-0.15 -1.05
-0.14 -0.98
-0.13 -0.65
-0.12 -0.6
-0.11 -0.33
-0.10 -0.3
-0.09 -0.2475
-0.08 -0.22
-0.07 -0.1925
-0.06 -0.12
-0.05 -0.1
-0.04 -0.08
-0.03 -0.03
-0.02 -0.02
-0.01 -0.01
0.00 0
0.01 0.005
0.02 0.01
0.03 0.015
0.04 0.02
0.05 0.025
0.06 0.03
0.07 0.035
0.08 0.04
0.09 0.045
0.10 0.05
0.11 0.055
0.12 0.06
0.13 0.065
0.14 0.07
0.15 0.075

You can see from the data that the rate of change in the Y values
increases quickly as X increases, hence my use of a logarithmic
trendline.

Graphing this in excel, selecting Add Trendline Logarithmic
Options Display equation on chart produces an excellent fit for the
data and the following equation for the fitted line:

y = 0.3359Ln(x) - 0.9859

But this doesn't make sense; trying to replicate the Y values based on
the equation will fail because you can't calculate Ln(x) if x is zero
or negative. Excel's help even specifically says that "a logarithmic
trendline can use both negative and positive values", but I don't
understand how this is possible.

I know that a transformation is normally required to model negative
data in a logarithmic setting, but why doesn't the displayed equation
describe the transformation?

Any help on this would be greatly appreciated.