CODEWITHSUNDEEP
regressionexcellogarithm
Nick
Nick

Reputation:

Logarithmic regression in Microsoft Excel

With reference to this regression line:

https://dl.dropboxusercontent.com/u/46186972/regression_line.gif

As shown in the bottom-right of this chart, the formula for the black regression line is:

y = 0.075ln(x) + 0.2775

How can I write this formula in Microsoft Excel, so that I can calculate y for any given value of x? (I tried calculating it using Excel help, however it did not give me the correct y values shown in the regression line chart linked above. But this could well be because I know little about regressions and nothing about logarithms.)

Upvotes: 1

Views: 12137

Answers (1)

gung - Reinstate Monica
gung - Reinstate Monica

Reputation: 11893

Let's say you have a column of numbers in column B, which represent your x values. They start on row 2 and go through row 11. Then, in column C row 2 you write =ln(B2) and drag that down to C11. In column D row 2 you write =0.075*C2 and drag that down to D11. Finally, in column E row 2 you write =D2+0.2775 and drag that down to E11. When you're done, the predicted y values for each x value will be in column E. The predicted y value in a given row of E will correspond to the x value in that same row in column B.

This way you can see the calculation unfolding. If you prefer to do it in one formula, you would write =0.075*ln(B2)+0.2775 in column C row 2 and drag it down to C11.

Upvotes: 2

Related Questions