Problem with trend line fitting

**enp1ld** · 04-29-2009, 01:56 PM

Hi all,

I came across this site after searching for many hours online to no avail, and I'm praying you guys can help. My problem is fitting a trend line to my data (hopefully attached). Now the data shown is my experimental data which I am trying to fit to a model. To test the data's conformity to this model a plot x/p vs x should result in a straight line (which it does). From this I can obtain further information about my data from the constants (gradient & y intercept). However plotting a linear trend line doesn't seem to result in a straight line (see attached). Could this be because the range of on my x axis is too large? A power trend line does fit the data but not the model. Am I using the wrong trend line, is it a problem with excel (doubtful I know!), could it be a problem with the logarithmic scales of the x and y axis?????

If anyone who can help or give insight I would be INCREDIBLY grateful.

Many thanks

PS Excel 2003

**shg** · 04-29-2009, 02:16 PM

If you want a fit that is linear in log-log space, then you need to do a log-log (power series) fit.

Select two cells, say A1 and B1, and array enter this: =LINEST(LN(C6:M6), LN(C4:M4))

That gives 0.8678 and -2.0914, meaning that the best-fit regression =EXP(-2.0914) * x ^ 0.8678. The 0.8678 is the exponent you see in your trendline, and =EXP(-2.0914) is 0.1235, the coefficient you see in your trendline.

If you want a linear fit, use =LINEST(C6:M6, C4:M4) to get the coefficients.

**enp1ld** · 05-04-2009, 03:11 PM

Hi shg, thank you for your reply.

I tried the method you gave me however the constants obtained using a power trendline yield results which don't make sense.

The gradient obtained using a linear trendline should be (when divided by 1) approximately 10 (which it is!). Using the gradient given by the power trendline when divided by 1 equals 1.25 (which cannot be right). So the gradient of the linear trendline is correct.

The real issue for me is that I am unable to get a straight line using a linear trendline which screws up my y intercept value (which is what I really need).

I tried setting the intercept to 0 which gives me the straight line (although if you look it doesn't actually put the intercept as 0 but as ~0.00001) but obviously no y intercept.

Is this a short coming in excel (as the scale is large) or my maths ability. More likely to be my maths!

Many thanks for your help.

**shg** · 05-04-2009, 04:03 PM

A linear trendline does not appear straight on a log-log plot.

If you want the y intercept from a linear trendline, enter the second LINEST formula I suggested as an array formula into a two-cell wide range and read it from the second cell. Or use the INTERCEPT function to get it from a simple formula.

You have not discovered a defect in Excel.