Trendline equation

**hannahlance** · 01-13-2010, 09:02 AM

hello everyone,
I have a problem solving my trendline equation where our teacher told us to do it manually by substituting the value of x to get y. The trendline has a polynomial type line where the equation has E's..I tried to use growth and trend but seems it doesn't work..or maybe there are some tricks that i don't know yet.Here's the data:
Discharge Depth Top Width Ave velocity

m3/s m m m/s
100 2.45 141.0 0.29
300 2.99 143.9 0.70
500 3.42 146.0 1.00
750 3.84 148.0 1.32
1000 4.20 149.8 1.59
2000 5.25 155.1 2.46
3000 6.17 159.8 3.04

I want to get the depth and velocity @ 40m3/s.. I got 2.85 at depth which I think it doesn't make sense using growth..please help me.

**Richard Buttrey** · 01-13-2010, 10:20 AM

Hi,

The 3rd order polynomial trendline equation is:
y = (c3 * x^3) + (c2 * x^2) + (c1 * x^1) + b

Hence with your data in A1:D8 with row 1 being the column labels.

The Excel formulae are:

c3:
=INDEX(LINEST($B$2:$B$8,$A$2:$A$8^{1,2,3}),1)
c2:
=INDEX(LINEST($B$2:$B$8,$A$2:$A$8^{1,2,3}),1,2)
C1:
=INDEX(LINEST($B$2:$B$8,$A$2:$A$8^{1,2,3}),1,3)
b:
=INDEX(LINEST($B$2:$B$8,$A$2:$A$8^{1,2,3}),1,4)

These give the results

C3: 1.64931E-10
C2: -1.02154E-06
C1: 0.002883531
b: 2.191774396

and plugging these into the trendline equation with a discharge of 40m3/s gives the result for depth of 2.305 and velocity of 0.204

Note for the velocity calc remember to change the B2:B8 reference to D2:D8

HTH

**hannahlance** · 01-13-2010, 02:44 PM

Hi richard,

I appreciate your help...you really got the answer..I just need a little bit more explanation on how to plug it...where did you get the equation? I had equations but different from what you have..please explain it to me more...thanks

**Richard Buttrey** · 01-13-2010, 08:31 PM

Hi Hanna,

If you google polynomial there are many maths sites which give you the general equations for polynomials. However you may find John Walkenbach's site useful since it also includes the Excel LINEST() functions as well.

http://spreadsheetpage.com/index.php...line_formulas/

I charted your data on an xy scattergraph and first tried a 2nd order polynomial trendline which wasn't quite as good a fit as a 3rd order. In fact for a discharge at 40m3/s it gave a depth of 2.46 which is slightly more than the 100m3/s. Hence I used the 3rd order in my answer. If you chart both 2nd and 3rd order trendlines you can see the difference quite noticeably where the 2000 m3/s plot is noticeably below the trendline.

HTH