Different coefficients from regression and trendline equation

A

A_Salem

New Member
#1
#1
I have two variables 'x' and 'y'. I took the natural log of the 'y' variable and then plotted the ln(y) vs x on a scatterplot in excel. I added a logarithmic trendline which seems to fit perfectly. The line equation is y = 1.1282*ln(x) + 12.183 with an R-Squared of 89%. However when I run the regression using the Data Analysis tool pack, I get a completely different set of coefficients and the R-Squared is 52%. Does anyone know why the coefficients from the trendline equation and the coefficients from running the regression are totally different?

Thanks in advance
 
K

katxt

Well-Known Member
#3
#3
A_Salem said:
I took the natural log of the 'y' variable and then plotted the ln(y) vs x on a scatterplot in excel. I added a logarithmic trendline which seems to fit perfectly. The line equation is y = 1.1282*ln(x) + 12.183
Click to expand...
Shouldn't the variables be x and ln(y) rather than y and ln(x)?
 
A

A_Salem

New Member
#4
#4
@katxt yes it should be ln(y). I guess since I added a logarithmic trend line, excel thinks my 'x' variable is in log form. I'm not sure if I can indicate that 'y' is in log form.
 
K

katxt

Well-Known Member
#5
#5
It seems that you may be doing things twice. Try this in Excel -
Start with the raw data x and y. Don't transform. Plot y up and x across.
If it looks straight put in a linear trendline and equation.
If it is not straight, change the y axis to a log scale. If it looks straight now, put in an exponential trendline and equation. This is the equation of the original data.
If that doesn't give a straight line, make the y axis normal again and make the x axis a log scale. If the graph looks straight, put in a logarithmic trend line with equation. This is the equation of the original data.
If this doesn't work, log both axes. If the graph looks straight now, put in a power trendline with its equation. This is the equation of the original data.
 
noetsi

noetsi

No cake for spunky
#8
#8
Dason said:
I mean I agree. But it's not as bad as it used to be. Did you ever think you'd think we me say that?
Click to expand...
lol no. But then did you ever think I would use R rather than SAS. :p

I had bad experiences with excel in the past and I don't think it lets you test the assumptions of methods which concerns me
 
A

A_Salem

New Member
#11
#11
katxt said:
It seems that you may be doing things twice. Try this in Excel -
Start with the raw data x and y. Don't transform. Plot y up and x across.
If it looks straight put in a linear trendline and equation.
If it is not straight, change the y axis to a log scale. If it looks straight now, put in an exponential trendline and equation. This is the equation of the original data.
If that doesn't give a straight line, make the y axis normal again and make the x axis a log scale. If the graph looks straight, put in a logarithmic trend line with equation. This is the equation of the original data.
If this doesn't work, log both axes. If the graph looks straight now, put in a power trendline with its equation. This is the equation of the original data.
Click to expand...
Thanks @katxt The first two options worked. First option had R-squared of 80% while the second option had R-squared of 40%. The last two options did not work because the log of some of the x values resulted in 0 and excel did not produce an equation.
 
You must log in or register to reply here.