# Different coefficients from regression and trendline equation

#### A_Salem

##### New Member
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?

#### Dason

Sounds like you weren't fitting the same model.

#### katxt

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

#### A_Salem

##### New Member
@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
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

##### No cake for spunky
Don't use excel for regression #### Dason

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?

#### noetsi

##### No cake for spunky
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. 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

#### katxt

##### Active Member
It's just exploratory stuff, and some folks have nothing else.

#### A_Salem

##### New Member
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.

#### noetsi

##### No cake for spunky
@noetsi I am actually learning R right now. It just takes me a while to google the right code lol.
Me to and I have SAS and other tools. It is worth it. We have a whole board here on R.