1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
|
<?xml version="1.0" encoding="UTF-8"?>
<helpdocument version="1.0">
<!--
* This file is part of the LibreOffice project.
*
* This Source Code Form is subject to the terms of the Mozilla Public
* License, v. 2.0. If a copy of the MPL was not distributed with this
* file, You can obtain one at http://mozilla.org/MPL/2.0/.
*
* This file incorporates work covered by the following license notice:
*
* Licensed to the Apache Software Foundation (ASF) under one or more
* contributor license agreements. See the NOTICE file distributed
* with this work for additional information regarding copyright
* ownership. The ASF licenses this file to you under the Apache
* License, Version 2.0 (the "License"); you may not use this file
* except in compliance with the License. You may obtain a copy of
* the License at http://www.apache.org/licenses/LICENSE-2.0 .
-->
<meta>
<topic id="textschart0104050100xml" indexer="include" status="PUBLISH">
<title id="tit" xml-lang="en-US">Trend Lines</title>
<filename>/text/schart/01/04050100.xhp</filename>
</topic>
</meta>
<body>
<bookmark xml-lang="en-US" branch="index" id="bm_id1744743">
<bookmark_value>calculating;regression curves</bookmark_value>
<bookmark_value>regression curves in charts</bookmark_value>
<bookmark_value>trend lines in charts</bookmark_value>
<bookmark_value>mean value lines in charts</bookmark_value>
</bookmark>
<comment>only use trend line, not regression curve: i89823</comment>
<bookmark xml-lang="en-US" branch="hid/.uno:InsertMenuTrendlines" id="bm_id2001709" localize="false"/>
<bookmark xml-lang="en-US" branch="hid/modules/schart/ui/tp_Trendline/TP_TRENDLINE" id="bm_id2001710" localize="false"/>
<paragraph id="hd_id5409405" role="heading" level="1" xml-lang="en-US"> <variable id="regression"><link href="text/schart/01/04050100.xhp">Trend Lines</link></variable></paragraph>
<paragraph id="par_id7272255" role="paragraph" xml-lang="en-US"> <variable id="trendlinestext"> <ahelp hid=".">Trend lines can be added to all 2D chart types except for Pie and Stock charts.</ahelp> </variable> </paragraph>
<section id="howtoget">
<embed href="text/schart/00/00000004.xhp#trendlines"/>
</section>
<comment>Linear</comment>
<bookmark xml-lang="en-US" branch="hid/modules/schart/ui/tp_Trendline/linear" id="bm_id6944181" localize="false"/>
<paragraph id="par_id5716727" role="paragraph" xml-lang="en-US"><ahelp hid="." visibility="hidden">A linear trend line is shown.</ahelp></paragraph>
<comment>Logarithmic</comment>
<bookmark xml-lang="en-US" branch="hid/modules/schart/ui/tp_Trendline/logarithmic" id="bm_id886257" localize="false"/>
<paragraph id="par_id5840021" role="paragraph" xml-lang="en-US"><ahelp hid="." visibility="hidden">A logarithmic trend line is shown.</ahelp></paragraph>
<comment>Exponential</comment>
<bookmark xml-lang="en-US" branch="hid/modules/schart/ui/tp_Trendline/exponential" id="bm_id8892483" localize="false"/>
<paragraph id="par_id9417096" role="paragraph" xml-lang="en-US"><ahelp hid="." visibility="hidden">An exponential trend line is shown.</ahelp></paragraph>
<comment>Power</comment>
<bookmark xml-lang="en-US" branch="hid/modules/schart/ui/tp_Trendline/power" id="bm_id7198400" localize="false"/>
<paragraph id="par_id8482924" role="paragraph" xml-lang="en-US"><ahelp hid="." visibility="hidden">A power trend line is shown.</ahelp></paragraph>
<comment>Polynomial</comment>
<bookmark xml-lang="en-US" branch="hid/modules/schart/ui/tp_Trendline/polynomial" id="bm_id180820161047491735" localize="false"/>
<paragraph id="par_id180820161052123210" role="paragraph" xml-lang="en-US"><ahelp hid="." visibility="hidden">A polynomial trend line is shown with a given degree.</ahelp></paragraph>
<comment>Degree</comment>
<bookmark xml-lang="en-US" branch="hid/modules/schart/ui/tp_Trendline/label3" id="bm_id180820161152296070" localize="false"/>
<paragraph id="par_id180820161102568315" role="paragraph" xml-lang="en-US"><ahelp hid="." visibility="hidden">Degree of polynomial trend line.</ahelp></paragraph>
<comment>Moving average</comment>
<bookmark xml-lang="en-US" branch="hid/modules/schart/ui/tp_Trendline/movingAverage" id="bm_id180820161104335824" localize="false"/>
<paragraph id="par_id180820161105546053" role="paragraph" xml-lang="en-US"><ahelp hid="." visibility="hidden">A moving average trend line is shown with a given period.</ahelp></paragraph>
<comment>Period</comment>
<bookmark xml-lang="en-US" branch="hid/modules/schart/ui/tp_Trendline/label4" id="bm_id180820161157368991" localize="false"/>
<paragraph id="par_id180820161107537745" role="paragraph" xml-lang="en-US"><ahelp hid="." visibility="hidden">Number of points to calculate average of moving average trend line.</ahelp></paragraph>
<comment>Trend line name</comment>
<bookmark xml-lang="en-US" branch="hid/modules/schart/ui/tp_Trendline/label5" id="bm_id180820161429301956" localize="false"/>
<paragraph id="par_id180820161112599880" role="paragraph" xml-lang="en-US"><ahelp hid="." visibility="hidden">Name of trend line in legend.</ahelp></paragraph>
<comment>Extrapolate forward</comment>
<bookmark xml-lang="en-US" branch="hid/modules/schart/ui/tp_Trendline/label7" id="bm_id180820161430141657" localize="false"/>
<paragraph id="par_id180820161117252261" role="paragraph" xml-lang="en-US"><ahelp hid="." visibility="hidden">Trend line is extrapolated for higher x-values.</ahelp></paragraph>
<comment>Extrapolate backward</comment>
<bookmark xml-lang="en-US" branch="hid/modules/schart/ui/tp_Trendline/label8" id="bm_id180820161504266451" localize="false"/>
<paragraph id="par_id18082016111837138" role="paragraph" xml-lang="en-US"><ahelp hid="." visibility="hidden">Trend line is extrapolated for lower x-values.</ahelp></paragraph>
<comment>Force intercept</comment>
<bookmark xml-lang="en-US" branch="hid/modules/schart/ui/tp_Trendline/setIntercept" id="bm_id180820161122038174" localize="false"/>
<paragraph id="par_id180820161124272765" role="paragraph" xml-lang="en-US"><ahelp hid="." visibility="hidden">For linear, polynomial and exponential trend lines, intercept value is forced to a given value.</ahelp></paragraph>
<comment>Intervept value</comment>
<bookmark xml-lang="en-US" branch="hid/modules/schart/ui/tp_Trendline/interceptValue" id="bm_id180820161125169514" localize="false"/>
<paragraph id="par_id180820161126064822" role="paragraph" xml-lang="en-US"><ahelp hid="." visibility="hidden">Value of intercept if it is forced.</ahelp></paragraph>
<comment>Show equation</comment>
<bookmark xml-lang="en-US" branch="hid/modules/schart/ui/tp_Trendline/showEquation" id="bm_id4724875" localize="false"/>
<paragraph id="par_id8962370" role="paragraph" xml-lang="en-US"><ahelp hid="." visibility="hidden">Shows the trend line equation next to the trend line.</ahelp></paragraph>
<comment>Show correlation coefficient (R2)</comment>
<bookmark xml-lang="en-US" branch="hid/modules/schart/ui/tp_Trendline/showCorrelationCoefficient" id="bm_id9068880" localize="false"/>
<paragraph id="par_id6889858" role="paragraph" xml-lang="en-US"><ahelp hid="." visibility="hidden">Shows the coefficient of determination next to the trend line.</ahelp></paragraph>
<comment>X name</comment>
<bookmark xml-lang="en-US" branch="hid/modules/schart/ui/tp_Trendline/label6" id="bm_id180820161515523577" localize="false"/>
<paragraph id="par_id180820161133305870" role="paragraph" xml-lang="en-US"><ahelp hid="." visibility="hidden">Name of X variable in trend line equation.</ahelp></paragraph>
<comment>Y name</comment>
<bookmark xml-lang="en-US" branch="hid/modules/schart/ui/tp_Trendline/label9" id="bm_id180820161516105398" localize="false"/>
<paragraph id="par_id180820161134155865" role="paragraph" xml-lang="en-US"><ahelp hid="." visibility="hidden">Name of Y variable in trend line equation.</ahelp></paragraph>
<paragraph id="par_id8398998" role="note" xml-lang="en-US">If you insert a trend line to a chart type that uses categories, like <emph>Line</emph> or <emph>Column</emph>, then the numbers 1, 2, 3, <emph>…</emph> are used as x-values to calculate the trend line. For such charts the XY chart type might be more suitable.</paragraph>
<list type="unordered">
<listitem>
<paragraph id="par_id4349192" role="paragraph" xml-lang="en-US">To insert a trend line for a data series, select the data series in the chart. Choose <item type="menuitem">Insert - Trend Line</item>, or right-click to open the context menu, and choose <item type="menuitem">Insert Trend Line</item>.</paragraph>
</listitem>
<listitem><bookmark xml-lang="en-US" branch="hid/.uno:InsertMenuMeanValues" id="bm_id7854243" localize="false"/>
<paragraph id="par_id180820161539033867" role="paragraph" xml-lang="en-US">Mean Value Lines are special trend lines that show the mean value. Use <item type="menuitem">Insert - Mean Value Lines</item> to insert mean value lines for data series.</paragraph>
<embed href="text/schart/01/04030000.xhp#context"/>
</listitem>
<listitem>
<paragraph id="par_id9337443" role="paragraph" xml-lang="en-US">To delete a trend line or mean value line, click the line, then press the Del key.</paragraph>
</listitem>
</list>
<paragraph id="par_id296334" role="note" xml-lang="en-US">A trend line is shown in the legend automatically. Its name can be defined in options of the trend line.</paragraph>
<paragraph id="par_id9569689" role="paragraph" xml-lang="en-US">The trend line has the same color as the corresponding data series. To change the line properties, select the trend line and choose <item type="menuitem">Format - Format Selection - Line</item>.</paragraph>
<bookmark xml-lang="en-US" branch="hid/.uno:InsertTrendlineEquationAndR2" id="bm_id2754601" localize="false"/>
<paragraph id="hd_id180820161534333508" role="heading" level="2" xml-lang="en-US">Trend Line Equation and Coefficient of Determination</paragraph>
<bookmark xml-lang="en-US" branch="hid/.uno:InsertTrendlineEquation" id="bm_id2754600" localize="false"/>
<paragraph id="par_id8962065" role="paragraph" xml-lang="en-US">When the chart is in edit mode, %PRODUCTNAME gives you the equation of the trend line and the coefficient of determination R<sup>2</sup>, even if they are not shown: click on the trend line to see the information in the status bar.</paragraph>
<paragraph id="par_id846888" role="paragraph" xml-lang="en-US"><ahelp hid=".">To show the trend line equation, select the trend line in the chart, right-click to open the context menu, and choose <item type="menuitem">Insert Trend Line Equation</item>.</ahelp></paragraph>
<paragraph id="par_id8962066" role="paragraph" xml-lang="en-US">To change format of values (use less significant digits or scientific notation), select the equation in the chart, right-click to open the context menu, and choose <item type="menuitem">Format Trend Line Equation - Numbers</item>.</paragraph>
<paragraph id="par_id180820161627109994" role="paragraph" xml-lang="en-US">Default equation uses <item type="literal">x</item> for abscissa variable, and <item type="literal">f(x)</item> for ordinate variable. To change these names, select the trend line, choose <item type="menuitem">Format - Format Selection – Type</item> and enter names in <item type="literal">X Variable Name</item> and <item type="literal">Y Variable Name</item> edit boxes.</paragraph>
<bookmark xml-lang="en-US" branch="hid/.uno:InsertR2Value" id="bm_id2754602" localize="false"/>
<paragraph id="par_id18082016163702791" role="paragraph" xml-lang="en-US">To show the coefficient of determination R<sup>2</sup>, select the equation in the chart, right-click to open the context menu, and choose <item type="menuitem">Insert R<sup>2</sup></item>.</paragraph>
<paragraph id="par_id180820161637028632" role="note" xml-lang="en-US">If intercept is forced, coefficient of determination R<sup>2</sup> is not calculated in the same way as with free intercept. R<sup>2</sup> values can not be compared with forced or free intercept.</paragraph>
<paragraph id="hd_id180820161534333509" role="heading" level="2" xml-lang="en-US">Trend Lines Curve Types</paragraph>
<paragraph id="par_id18082016153820777" role="paragraph" xml-lang="en-US">Following regression types are available:</paragraph>
<list type="unordered">
<listitem>
<paragraph id="par_id180820161604098009" role="paragraph" xml-lang="en-US"><emph>Linear</emph> trend line: regression through equation <item type="literal">y=a∙x+b</item>. Intercept <item type="literal">b</item> can be forced.</paragraph>
</listitem>
<listitem>
<paragraph id="par_id180820161612524298" role="paragraph" xml-lang="en-US"><emph>Polynomial</emph> trend line: regression through equation <item type="literal">y=Σ(a<sub>i</sub>∙x<sup>i</sup>)</item>. Intercept <item type="literal">a<sub>0</sub></item> can be forced. Degree of polynomial must be given (at least 2).</paragraph>
</listitem>
<listitem>
<paragraph id="par_id180820161612525364" role="paragraph" xml-lang="en-US"><emph>Logarithmic</emph> trend line: regression through equation <item type="literal">y=a∙ln(x)+b</item>.</paragraph>
</listitem>
<listitem>
<paragraph id="par_id180820161612526680" role="paragraph" xml-lang="en-US"><emph>Exponential</emph> trend line: regression through equation <item type="literal">y=b∙exp(a∙x)</item>.This equation is equivalent to <item type="literal">y=b∙m<sup>x</sup></item> with <item type="literal">m=exp(a)</item>. Intercept <item type="literal">b</item> can be forced.</paragraph>
</listitem>
<listitem>
<paragraph id="par_id180820161612527230" role="paragraph" xml-lang="en-US"><emph>Power</emph> trend line: regression through equation <item type="literal">y=b∙x<sup>a</sup></item>.</paragraph>
</listitem>
<listitem>
<paragraph id="par_id180820161617342768" role="paragraph" xml-lang="en-US"><emph>Moving average</emph> trend line: simple moving average is calculated with the <emph>n</emph> previous y-values, <emph>n</emph> being the period. No equation is available for this trend line.</paragraph>
</listitem></list>
<paragraph id="hd_id9204077" role="heading" level="2" xml-lang="en-US">Constraints</paragraph>
<paragraph id="par_id7393719" role="paragraph" xml-lang="en-US"> The calculation of the trend line considers only data pairs with the following values:</paragraph>
<list type="unordered">
<listitem>
<paragraph id="par_id7212744" role="paragraph" xml-lang="en-US">Logarithmic trend line: only positive x-values are considered.</paragraph>
</listitem>
<listitem>
<paragraph id="par_id1664479" role="paragraph" xml-lang="en-US">Exponential trend line: only positive y-values are considered, except if all y-values are negative: regression will then follow equation <item type="literal">y=-b∙exp(a∙x)</item>.</paragraph>
</listitem>
<listitem>
<paragraph id="par_id8734702" role="paragraph" xml-lang="en-US">Power trend line: only positive x-values are considered; only positive y-values are considered, except if all y-values are negative: regression will then follow equation<item type="literal"> y=-b∙x<sup>a</sup></item>.</paragraph>
</listitem>
</list>
<paragraph id="par_id181279" role="paragraph" xml-lang="en-US">You should transform your data accordingly; it is best to work on a copy of the original data and transform the copied data.</paragraph>
<paragraph id="hd_id180820161528021520" role="heading" level="2" xml-lang="en-US">Calculate Parameters in Calc</paragraph>
<paragraph id="par_id7735221" role="paragraph" xml-lang="en-US">You can also calculate the parameters using Calc functions as follows.</paragraph>
<paragraph id="hd_id5744193" role="heading" level="3" xml-lang="en-US">The linear regression equation</paragraph>
<paragraph id="par_id9251991" role="paragraph" xml-lang="en-US">The <emph>linear regression</emph> follows the equation <item type="literal">y=m*x+b</item>.</paragraph>
<paragraph id="par_id7951902" role="code" xml-lang="en-US">m = SLOPE(Data_Y;Data_X) </paragraph>
<paragraph id="par_id6637165" role="code" xml-lang="en-US">b = INTERCEPT(Data_Y ;Data_X) </paragraph>
<paragraph id="par_id7879268" role="paragraph" xml-lang="en-US">Calculate the coefficient of determination by</paragraph>
<paragraph id="par_id9244361" role="code" xml-lang="en-US">r<sup>2</sup> = RSQ(Data_Y;Data_X) </paragraph>
<paragraph id="par_id2083498" role="paragraph" xml-lang="en-US">Besides m, b and r<sup>2</sup> the array function <emph>LINEST</emph> provides additional statistics for a regression analysis.</paragraph>
<paragraph id="hd_id2538834" role="heading" level="3" xml-lang="en-US">The logarithmic regression equation</paragraph>
<paragraph id="par_id394299" role="paragraph" xml-lang="en-US">The <emph>logarithmic regression</emph> follows the equation <item type="literal">y=a*ln(x)+b</item>.</paragraph>
<paragraph id="par_id2134159" role="code" xml-lang="en-US">a = SLOPE(Data_Y;LN(Data_X)) </paragraph>
<paragraph id="par_id5946531" role="code" xml-lang="en-US">b = INTERCEPT(Data_Y ;LN(Data_X)) </paragraph>
<paragraph id="par_id5649281" role="code" xml-lang="en-US">r<sup>2</sup> = RSQ(Data_Y;LN(Data_X)) </paragraph>
<paragraph id="hd_id7874080" role="heading" level="3" xml-lang="en-US">The exponential regression equation</paragraph>
<paragraph id="par_id4679097" role="paragraph" xml-lang="en-US"> For exponential trend lines a transformation to a linear model takes place. The optimal curve fitting is related to the linear model and the results are interpreted accordingly.</paragraph>
<paragraph id="par_id9112216" role="paragraph" xml-lang="en-US">The exponential regression follows the equation <item type="literal">y=b*exp(a*x)</item> or <item type="literal">y=b*m^x</item>, which is transformed to <item type="literal">ln(y)=ln(b)+a*x</item> or <item type="literal">ln(y)=ln(b)+ln(m)*x</item> respectively.</paragraph>
<paragraph id="par_id4416638" role="code" xml-lang="en-US">a = SLOPE(LN(Data_Y);Data_X) </paragraph>
<paragraph id="par_id1039155" role="paragraph" xml-lang="en-US">The variables for the second variation are calculated as follows:</paragraph>
<paragraph id="par_id7184057" role="code" xml-lang="en-US">m = EXP(SLOPE(LN(Data_Y);Data_X)) </paragraph>
<paragraph id="par_id786767" role="code" xml-lang="en-US">b = EXP(INTERCEPT(LN(Data_Y);Data_X)) </paragraph>
<paragraph id="par_id7127292" role="paragraph" xml-lang="en-US">Calculate the coefficient of determination by</paragraph>
<paragraph id="par_id5437177" role="code" xml-lang="en-US">r<sup>2</sup> = RSQ(LN(Data_Y);Data_X) </paragraph>
<paragraph id="par_id6946317" role="paragraph" xml-lang="en-US">Besides m, b and r<sup>2</sup> the array function LOGEST provides additional statistics for a regression analysis.</paragraph>
<paragraph id="hd_id6349375" role="heading" level="3" xml-lang="en-US">The power regression equation</paragraph>
<paragraph id="par_id1857661" role="paragraph" xml-lang="en-US"> For <emph>power regression</emph> curves a transformation to a linear model takes place. The power regression follows the equation <item type="literal">y=b*x^a</item> , which is transformed to <item type="literal">ln(y)=ln(b)+a*ln(x)</item>.</paragraph>
<paragraph id="par_id8517105" role="code" xml-lang="en-US">a = SLOPE(LN(Data_Y);LN(Data_X)) </paragraph>
<paragraph id="par_id9827265" role="code" xml-lang="en-US">b = EXP(INTERCEPT(LN(Data_Y);LN(Data_X)) </paragraph>
<paragraph id="par_id2357249" role="code" xml-lang="en-US">r<sup>2</sup> = RSQ(LN(Data_Y);LN(Data_X)) </paragraph>
<paragraph id="hd_id7907040" role="heading" level="3" xml-lang="en-US">The polynomial regression equation</paragraph>
<paragraph id="par_id8918729" role="paragraph" xml-lang="en-US">For <emph>polynomial regression</emph> curves a transformation to a linear model takes place.</paragraph>
<paragraph id="par_id33875" role="paragraph" xml-lang="en-US">Create a table with the columns x, x<sup>2</sup>, x<sup>3</sup>, … , x<sup>n</sup>, y up to the desired degree n. </paragraph>
<paragraph id="par_id8720053" role="paragraph" xml-lang="en-US">Use the formula <item type="literal">=LINEST(Data_Y,Data_X)</item> with the complete range x to x<sup>n</sup> (without headings) as Data_X. </paragraph>
<paragraph id="par_id5068514" role="paragraph" xml-lang="en-US">The first row of the LINEST output contains the coefficients of the regression polynomial, with the coefficient of xⁿ at the leftmost position.</paragraph>
<paragraph id="par_id8202154" role="paragraph" xml-lang="en-US">The first element of the third row of the LINEST output is the value of r<sup>2</sup>. See the <link href="text/scalc/01/04060107.xhp#Section8">LINEST</link> function for details on proper use and an explanation of the other output parameters.</paragraph>
<section id="relatedtopics">
<paragraph id="par_id4562211" role="paragraph" xml-lang="en-US"><link href="text/schart/01/04050000.xhp">X/Y Error Bars</link></paragraph>
<paragraph id="par_id4562212" role="paragraph" xml-lang="en-US"><link href="text/scalc/01/04060107.xhp#Section8">LINEST</link> function</paragraph>
<paragraph id="par_id4562216" role="paragraph" xml-lang="en-US"><link href="text/scalc/01/04060107.xhp#Section7">LOGEST</link> function</paragraph>
<paragraph id="par_id4562213" role="paragraph" xml-lang="en-US"><link href="text/scalc/01/04060185.xhp#slope">SLOPE</link> function</paragraph>
<paragraph id="par_id4562214" role="paragraph" xml-lang="en-US"><link href="text/scalc/01/04060181.xhp#intercept">INTERCEPT</link> function</paragraph>
<paragraph id="par_id4562215" role="paragraph" xml-lang="en-US"><link href="text/scalc/01/04060181.xhp#rsq">RSQ</link> function</paragraph>
</section>
</body>
</helpdocument>
|
