Different coefficients from regression and trendline equation

#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
 

katxt

Active Member
#3
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
Shouldn't the variables be x and ln(y) rather than y and ln(x)?
 
#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.
 

katxt

Active Member
#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

Fortran must die
#8
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?
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
 
#11
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.
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.