Calculation of Slope & Intecept of Logarithmic Trendline

[gammyleg]

Hi,

I have produced an x-y scatter chart from the following two columns of data.

[TABLE="width: 159"]
<tbody>[TR]
[TD]åkDt hours[/TD]
[TD]Comp. Strength (N/mm²)[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 159"]
<tbody>[TR]
[TD]11.36[/TD]
[TD]6.2[/TD]
[/TR]
[TR]
[TD]16.31[/TD]
[TD]12.9[/TD]
[/TR]
[TR]
[TD]25.18[/TD]
[TD]18.4[/TD]
[/TR]
[TR]
[TD]29.36[/TD]
[TD]24.4[/TD]
[/TR]
[TR]
[TD]38.21[/TD]
[TD]30.5[/TD]
[/TR]
[TR]
[TD]88.41[/TD]
[TD]41.4[/TD]
[/TR]
[TR]
[TD]349.29[/TD]
[TD]63.6[/TD]
[/TR]
</tbody>[/TABLE]

The X scale is formatted to Logarithmic Base 10 and I have added a logarithmic trendline which also displays slope/intercept equation on the chart. In this case it is:

y = 16.72ln(x) -33.445

I then have to manually input these figures elsewhere to carry out further calculations.

Can someone please tell me how to calculate to two numbers without having to produce the chart and trendline.

Thanks in advance.

 

[Jon Peltier]

Don't forget that these two are equivalent formulas:
y = 16.72 ln(x) - 33.445
y = 38.50 log(x) - 33.445

38.50/16.72 = ln(10) = 2.303

I show both of these since you noted that you used a log-base-ten axis scale.

You can use SLOPE() and INTERCEPT() to get the formula coefficients.

Assuming your X and Y values are in B2:B8 and C2:C8, you can use these array formulas:

{=SLOPE(C2:C8,LOG(B2:B8))}
{=INTERCEPT(C2:C8,LOG(B2:B8))}

which produce 38.50 and -33.445, or

{=SLOPE(C2:C8,LN(B2:B8))}
{=INTERCEPT(C2:C8,LN(B2:B8))}

which produce 16.72 and -33.445.

Don't type the curly brackets; hold Ctrl+Shift while pressing Enter and Excel puts them there to show these are curly brackets.

Alternatively, in cell A2 enter =LOG(B2) or =LN(B2) and fill it down to A8 and use non-array formulas

=SLOPE(C2:C8,A2:A8)
=INTERCEPT(C2:C8,A2:A8)

and you'll get 38.50 or 16.72 as slope and -33.445 as intercept.

 

[thisoldman]

John Walkenbach, from http://spreadsheetpage.com/index.php/tip/chart_trendline_formulas/, gives this set of formulas:

[A]ssume that your sheet has two named ranges: x and y.

Logarithmic Trendline
Equation: y = (c * LN(x)) + b
c: =INDEX(LINEST(y,LN(x)),1)
b: =INDEX(LINEST(y,LN(x)),1,2)

With your data, c = 16.72091 and b = -33.489.

I'll note that the trend line coefficients computed using LINEST are not always exactly what Excel uses in the chart. Jon Peltier's equations are conform more exactly to the chart equation.

 

[gammyleg]

Jon,

Thanks for your help. Worked perfectly.

 

[Jon Peltier]

I'll note that the trend line coefficients computed using LINEST are not always exactly what Excel uses in the chart.

This depends on two things.

1. Older versions of Excel had issues with statistical calculations.

2. The trendline formula may truncate the coefficients with fewer digits than needed for accurate use via copying and pasting into the worksheet.