trend line no chart.

[montecarlo2012]

I use trend function just write =trend(A1:A20) enter and I got the linear trend, my question, if I want the polynomial trend, what I have to write?
=what?.

 

[10R]

I have never used this function before, but a quick test of the function looks like it deliers a slope value. You have only specified one column (A1 to A20)so I think the function assumes all you x values are the same (i.e. same distance apart on a graph). Polynomials require x and y values. You must have an array.

For a polynomial I would try

Add x values that correlate to column A into column B. Then square your x values in c1 to c20, cubing you x values in d1 to d20...etc to the number of coeficients you desire. Then select col. A for Y values, and all the column X columns for the xrange.

Don't know how the trend funtion will handle that, but using the regression option in the data analysis tools will work great. If you select the entire X array as the X range, the regression analysis tool will spit out all the coefficients for your polynomial.

Hope this helps

 

[montecarlo2012]

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.25pt; BORDER-TOP: windowtext 1.5pt solid; BORDER-RIGHT: windowtext 1.5pt solid" class=xl24 height=19 width=64 align=right x:num>[FONT=Arial][SIZE=2]3[/SIZE][/FONT]</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl24 height=19 align=right x:num>[FONT=Arial][SIZE=2]11[/SIZE][/FONT]</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl24 height=19 align=right x:num>[FONT=Arial][SIZE=2]5[/SIZE][/FONT]</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl24 height=19 align=right x:num>[FONT=Arial][SIZE=2]14[/SIZE][/FONT]</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl24 height=19 align=right x:num>[FONT=Arial][SIZE=2]22[/SIZE][/FONT]</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl24 height=19 align=right x:num>[FONT=Arial][SIZE=2]15[/SIZE][/FONT]</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl24 height=19 align=right x:num>[FONT=Arial][SIZE=2]2[/SIZE][/FONT]</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl24 height=19 align=right x:num>[FONT=Arial][SIZE=2]2[/SIZE][/FONT]</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl24 height=19 align=right x:num>[FONT=Arial][SIZE=2]9[/SIZE][/FONT]</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl24 height=19 align=right x:num>[FONT=Arial][SIZE=2]4[/SIZE][/FONT]</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl24 height=19>[FONT=Arial][SIZE=2] [/SIZE][/FONT]</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl24 height=19 align=right x:num="10.418181818181818" x:fmla="=TREND(A1:A10)">[FONT=Arial][SIZE=2]10.41818[/SIZE][/FONT]</TD></TR></TBODY></TABLE>

thanks 10r. let me show you .

I just write =trend(A1:A10) drag down, and when I use XY scatter chart in drag down one colum no the array, thats why, so I was thinking to do the same with the others lines, I am not concern about flow average because is just add the last two number and divide by two, simple.

 

[montecarlo2012]

I have never used this function before, but a quick test of the function looks like it deliers a slope value. You have only specified one column (A1 to A20)so I think the function assumes all you x values are the same (i.e. same distance apart on a graph). Polynomials require x and y values. You must have an array.

For a polynomial I would try

Add x values that correlate to column A into column B. Then square your x values in c1 to c20, cubing you x values in d1 to d20...etc to the number of coeficients you desire. Then select col. A for Y values, and all the column X columns for the xrange.

Don't know how the trend funtion will handle that, but using the regression option in the data analysis tools will work great. If you select the entire X array as the X range, the regression analysis tool will spit out all the coefficients for your polynomial.

Hope this helps

the X values always are the natural numbers (1,2,3 etc in the spreadsheet)

 

[Mungyo]

For a quadratic trend I think you want to put into C2:C11

=TREND(A2:A11,(ROW(A2:A11)-ROW(A2))^{1,2})

which gives :

<table border="0" cellpadding="0" cellspacing="0" width="192"><colgroup><col style="width:48pt" width="64"> <col style="width:48pt" width="64"> <col style="width:48pt" width="64"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;width:48pt" height="20" width="64">Y</td> <td style="width:48pt" width="64">
</td> <td class="xl65" style="width:48pt" width="64">QUADRATIC</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt" height="20">3</td> <td>
</td> <td class="xl65" align="right">4.92</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt" height="20">11</td> <td>
</td> <td class="xl65" align="right">8.20</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt" height="20">5</td> <td>
</td> <td class="xl65" align="right">10.57</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt" height="20">14</td> <td>
</td> <td class="xl65" align="right">12.02</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt" height="20">22</td> <td>
</td> <td class="xl65" align="right">12.56</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt" height="20">15</td> <td>
</td> <td class="xl65" align="right">12.18</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt" height="20">2</td> <td>
</td> <td class="xl65" align="right">10.88</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt" height="20">2</td> <td>
</td> <td class="xl65" align="right">8.66</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt" height="20">9</td> <td>
</td> <td class="xl65" align="right">5.53</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt" height="20">4</td> <td>
</td> <td class="xl65" align="right">1.48</td> </tr> </tbody></table>

-- removed inline image ---

 

[montecarlo2012]

I tryied and I can't get your result, your press ctrl-shift-enter tell me please the steps sorry .

 

[Mungyo]

I find the easiest way to do ctrl-shift-enter with a long formula is

Put the formula I gave into C2 and press enter
Select the range from C2 to C11 with mouse
Press F2
Hold down Ctrl and Shift and then press Enter

 

[montecarlo2012]

thnks, any vba code to do this, I have to do this thounsand times.