Logarithmic Trendline

[jonathan.laberge]

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.

 

[Del Cotter]

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.

You had me really puzzled for a second there, because when I tried it,
the logarithmic trendline wasn't available to me at all! Which is as it
should be, as I understand it. The program had correctly noted that the
data was not capable of fitting to that or a number of other forms. Only
linear, polynomial and moving average could be selected.

Then the penny dropped, I made the required change, and reproduced your
fit equation perfectly: y = 0.33594Ln(x) - 0.98594

You've got a line chart type. The x axis is a category axis that just
reproduces all the x values in a line of labels, but really the values
the chart is using are 1,2,3,4, etc. Naturally the log trend fit works,
because the x values it sees are all positive. But the equation is not a
good prediction of what y will be, given x, because it's not working
from the correct values of x.

Change your chart type to Scatter (XY) Chart, and you'll find the log
trend is no longer available.

 

[Bernard Liengme]

The reason for this 'odd' result is that you have make a Line chart when you
needed an XY chart. In a Line chart the x-category values are treated as
ordinals (1,2,3,4) no matter what is displayed. You are not the first to
fall into this pit - Microsoft really needs a better name for Line charts
('Category' chart would do)

When I make an XY chart of your data it is clearly not logarithmic. (Further
proof of this: I transposed the values to get rid of negatives and still
Excel does not offer a log trendline)
It might fit a logistics curve (Google to find meaning)

Depending on your need, I would be temped to use a fourth-order polynomial.

best wishes