'from: http://spreadsheetpage.com/index.php/tip/chart_trendline_formulas/
'When you add a trendline to a chart, Excel provides an option to display the trendline equation in the chart. This tip describes how to create formulas that generate the trendline coefficients. You can then use these formulas to calculate predicted y values for give values of x.
'
'These equations assume that your sheet has two named ranges: x and y.
'
'Linear Trendline
'Equation: Y = m * X + b
'm: =SLOPE(y,x)
'b: =INTERCEPT(y,x)
'Logarithmic Trendline
'Equation: Y = (c * Ln(X)) + b
'c: =INDEX(LINEST(y,LN(x)),1)
'b: =INDEX(LINEST(y,LN(x)),1,2)
'Power Trendline
'Equation: Y = c * X ^ b
'c: =EXP(INDEX(LINEST(LN(y),LN(x),,),1,2))
'b: =INDEX(LINEST(LN(y),LN(x),,),1)
'Exponential Trendline
'Equation: Y = c e ^ (b X)
'c: =EXP(INDEX(LINEST(LN(y),x),1,2))
'b: =INDEX(LINEST(LN(y),x),1)
'2nd Order Polynomial Trendline
'Equation: Y = (c2 X ^ 2) + (C1 X ^ 1) + b
'c2: =INDEX(LINEST(y,x^{1,2}),1)
'C1: =INDEX(LINEST(y,x^{1,2}),1,2)
'b = =INDEX(LINEST(y,x^{1,2}),1,3)
'3rd Order Polynomial Trendline
'Equation: Y = (c3 X ^ 3) + (c2 X ^ 2) + (C1 * X ^ 1) + b
'c3: =INDEX(LINEST(y,x^{1,2,3}),1)
'c2: =INDEX(LINEST(y,x^{1,2,3}),1,2)
'C1: =INDEX(LINEST(y,x^{1,2,3}),1,3)
'b: =INDEX(LINEST(y,x^{1,2,3}),1,4)
'Higher Order Polynomial Trendline
'Notice the pattern in the two preceding sets of formulas.
'from: http://spreadsheetpage.com/index.php/tip/chart_trendline_formulas/ 'When you add a trendline to a chart, Excel provides an option to display the trendline equation in the chart. This tip describes how to create formulas that generate the trendline coefficients. You can then use these formulas to calculate predicted y values for give values of x. ' 'These equations assume that your sheet has two named ranges: x and y. ' 'Linear Trendline 'Equation: Y = m * X + b 'm: =SLOPE(y,x) 'b: =INTERCEPT(y,x)
'Logarithmic Trendline 'Equation: Y = (c * Ln(X)) + b 'c: =INDEX(LINEST(y,LN(x)),1) 'b: =INDEX(LINEST(y,LN(x)),1,2)
'Power Trendline 'Equation: Y = c * X ^ b 'c: =EXP(INDEX(LINEST(LN(y),LN(x),,),1,2)) 'b: =INDEX(LINEST(LN(y),LN(x),,),1)
'Exponential Trendline 'Equation: Y = c e ^ (b X) 'c: =EXP(INDEX(LINEST(LN(y),x),1,2)) 'b: =INDEX(LINEST(LN(y),x),1)
'2nd Order Polynomial Trendline 'Equation: Y = (c2 X ^ 2) + (C1 X ^ 1) + b 'c2: =INDEX(LINEST(y,x^{1,2}),1) 'C1: =INDEX(LINEST(y,x^{1,2}),1,2) 'b = =INDEX(LINEST(y,x^{1,2}),1,3)
'3rd Order Polynomial Trendline 'Equation: Y = (c3 X ^ 3) + (c2 X ^ 2) + (C1 * X ^ 1) + b 'c3: =INDEX(LINEST(y,x^{1,2,3}),1) 'c2: =INDEX(LINEST(y,x^{1,2,3}),1,2) 'C1: =INDEX(LINEST(y,x^{1,2,3}),1,3) 'b: =INDEX(LINEST(y,x^{1,2,3}),1,4)
'Higher Order Polynomial Trendline 'Notice the pattern in the two preceding sets of formulas.