www.excelcalcs.com - Making Excel Read Graphical and Tabular Data

Member Login

Or call us
US +1 (617) 500-8224
EU +44 (113) 815 2220
Or Skype us

Our Services

ï»¿

Total Downloads

156788

Home

Showcase

Making Excel Read Graphical and Tabular Data

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.

1) First the Easy Way

Note - best double click the player to show in maximised view.

2) Now the More Complex 'Dynamic' Way

Note - best double click the player to show in maximised view.

The following cell formulae will dynamically calculate trendline equation parameters from a data table. The trendline formulae are quite complicated so copy and paste into your worksheet. These equations assume that your sheet contains C5:H5 (known_x's) and C8:H8 (known_y's).

Linear Trendline
Equation: y = m * x + b
m: =SLOPE(C8:H8,C5:H5)
b: =INTERCEPT(C8:H8,C5:H5)

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

Power Trendline
Equation: y=c*x^b
c: =EXP(INDEX(LINEST(LN(C8:H8),LN(C5:H5),,),1,2))
b: =INDEX(LINEST(LN(C8:H8),LN(C5:H5),,),1)

Exponential Trendline
Equation: y = c *e ^(b * x)
c: =EC5:H5P(INDEX(LINEST(LN(C8:H8),C5:H5),1,2))
b: =INDEX(LINEST(LN(C8:H8),C5:H5),1)

2nd Order Polynomial Trendline
Equation: y = (c2 * x^2) + (c1 * x ^1) + b
c2: =INDEX(LINEST(C8:H8,C5:H5^{1,2}),1)
C1: =INDEX(LINEST(C8:H8,C5:H5^{1,2}),1,2)
b = =INDEX(LINEST(C8:H8,C5:H5^{1,2}),1,3)

3rd Order Polynomial Trendline
Equation: y = (c3 * x^3) + (c2 * x^2) + (c1 * x^1) + b
c3: =INDEX(LINEST(C8:H8,C5:H5^{1,2,3}),1)
c2: =INDEX(LINEST(C8:H8,C5:H5^{1,2,3}),1,2)
C1: =INDEX(LINEST(C8:H8,C5:H5^{1,2,3}),1,3)
b: =INDEX(LINEST(C8:H8,C5:H5^{1,2,3}),1,4)

Higher Order Polynomial Trendline
Follow the pattern set in the second and third order polynomial trendlines.

In the Forum

Read MajorMagee's comments and example spreadsheet.

Some Sample Calculations on the Site