# Excel – Logarithmic regression in Microsoft Excel

excellogarithmregression

With reference to this regression line:

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.)

#### Best Solution

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`.