Scribd
Upload
279 views1 page

Excel Trendline Formulas Guide

This document provides formulas for calculating the trendline coefficients for different types of trendlines in Excel charts. It gives the equations and formulas for linear, logarithmic, power, exponential, 2nd order polynomial, and 3rd order polynomial trendlines. The formulas use functions like SLOPE, INTERCEPT, LINEST, and INDEX to calculate the coefficients for the trendline equations based on named ranges for the x and y data.

Uploaded by

tiliarou
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
279 views1 page

Excel Trendline Formulas Guide

This document provides formulas for calculating the trendline coefficients for different types of trendlines in Excel charts. It gives the equations and formulas for linear, logarithmic, power, exponential, 2nd order polynomial, and 3rd order polynomial trendlines. The formulas use functions like SLOPE, INTERCEPT, LINEST, and INDEX to calculate the coefficients for the trendline equations based on named ranges for the x and y data.

Uploaded by

tiliarou
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Spreadsheet Page Excel Tips: Chart Trendline Formulas

http://spreadsheetpage.com/index.php/tip/chart_trendline_formulas/

Chart Trendline Formulas


Category: Formulas / Charts & Graphics | [Item URL]

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.

1 sur 1

20/03/2013 12:17

You might also like