tag:blogger.com,1999:blog-8544609315733972726.post-5709477946779363402007-12-15T10:44:00.005-07:002008-04-23T20:51:58.930-06:00Display equations for regression lines<p>For while, charts in Calc could do regression analysis, and starting in <a href="/search/label/openoffice.org%202.4">OpenOffice.org 2.4</a>, there is an easy way to display the equation to the chart.</p>
<img src="http://lh6.google.com/OOoNinja/R-UQvhZy4hI/AAAAAAAAAtQ/p67u3T4B7kg/s512/calc240_regression_function.png" alt="Regression analysis in OpenOffice.org Calc Chart 2.4.0" />
<p>In case statistics class is a fuzzy memory for you, a regression curve calculates a mathematical function to describe a set of data. Regression is helpful with predictions. For example, looking at the chart on the right. There are no values for a woman 80 inches tall, but using either the curve or its function, her height could be extrapolated to about 198 lbs.</p>
<h3>How to do it</h3>
<ol>
<li>Create an XY (Scatter) Chart.</li>
<li>Click off the chart to make sure the chart is not selected.</li>
<li>Double click the chart to enter chart edit mode.</li>
<li>Click <b>Insert->Statistic</b>.
<br/><img src="http://bp0.blogger.com/_1XYQfEGGEIw/R2U-kuwrZhI/AAAAAAAAAA8/Z8nCqiSv8c8/s512/calc240_insert_statistic.png" alt="OpenOffice.org Chart 2.4.0: statistics dialog box" /></li>
<li>Choose the type of regression line you want.</li>
<li>Carefully right click the regression line.</li>
<li>Choose <b>Insert Regression Curve Equation</b>.
<br/><img style="margin: 0px auto 10px; display: block; text-align: center; cursor: pointer;" src="http://bp0.blogger.com/_1XYQfEGGEIw/R2U_XuwrZiI/AAAAAAAAABE/RYtLcC4n7rw/s400/calc240_regression_function_context_menu.png" alt="OpenOffice.org 2.4.0 Chart: context menu" /></li>
</ol>
<h3>Increasing precision</h3>
<p>By default, the coefficient of determination R<sup>2</sup> and equation have little precision. To add decimal places:</p>
<ol>
<li>Enter chart mode if you are not already in it.</li>
<li>Right click on the equation and/or <sup>2</sup></li>
<li>Select <b>Object Properties</b>.</li>
<li>Click the <b>Numbers</b> tab.</li>
<li>Choose the category <b>Number</b>.</li>
<li>Format as desired.</li>
</ol>
<h3>Status bar</h3>
<p>Since before OpenOffice.org 2.4, the regression curve equation and coefficient of determination R<sup>2</sup> are visible by clicking on the curve.</p>
<img src="http://lh3.google.com/OOoNinja/R-5yghZy44I/AAAAAAAAA0U/rDEToPHteBE/s512/regression3_status_bar.png" alt="OpenOffice.org 2.4: Regression curve equation and coefficient of determination shown in status bar" />
<h3>Development snapshot</h3>
<p>Please keep in mind I am using an early OpenOffice.org 2.4.0 development snapshot 680m239. Before the final release in March 2008, the code will be more polished.</p>
<h3>Compared to Microsoft Excel</h3>
<img src="http://bp2.blogger.com/_1XYQfEGGEIw/R2U1MOwrZdI/AAAAAAAAAAc/n4AIQPBrfA4/s320/excel2003_intrepretation.png" alt="Screenshot: Chart made by OpenOffice.org 2.4.0 in Microsoft Office Excel 2003" />
<p>There are a few differences between Excel 2003 and OpenOffice.org.</p>
<ol><li>The proportions of the chart change importing into Excel. The image above is from the Excel 2003 application imported from OpenOffice.org.</li>
<li>Excel uses the term trendline instead of regression curve equation.</li>
<li>Both applications allow the use of the context menu (right click) to add, clear, and configure trendlines, but OpenOffice.org also has the <b>Insert->Statistics</b> menu option.</li>
<li>OpenOffice.org's context menu for adding the function is limited. It simply adds a linear regression curve.
</li>
<li>Excel 2003 allows changing the trendline between types (e.g., from linear to exponential) by clicking on the trendline, but in OpenOffice.org, you must choose the data series instead (<a rel="external nofollow" href="http://qa.openoffice.org/issues/show_bug.cgi?id=44768">issue 44768</a>).</li>
<li>Excel 2003 shows more precision by default than OpenOffice.org, but you can add precision by right clicking on the equation. Then, choose <b>Object Properties</b>. Then, go the <b>Numbers</b> tab. Then, change <b>Category</b> to <b>Number</b>. Then, set <b>Decimal places</b>.</li>
<li>Excel 2003 has a few more configuration options such as moving average (<a rel="external nofollow" href="http://qa.openoffice.org/issues/show_bug.cgi?id=66819">issue 66819</a>), polynomial regression (<a rel="external nofollow" href="http://qa.openoffice.org/issues/show_bug.cgi?id=20819">issue 20819</a>), forecast units (<a rel="external nofollow" href="http://qa.openoffice.org/issues/show_bug.cgi?id=5085">issue 5085</a>), and intercept (<a rel="external nofollow" href="http://qa.openoffice.org/issues/show_bug.cgi?id=77932">issue 77932</a>). The following two images are Excel 2003.
</br><img src="http://bp0.blogger.com/_1XYQfEGGEIw/R2U8YuwrZfI/AAAAAAAAAAs/nW7xPTCyxSo/s320/excel2003_add_trendline1.png" alt="Excel 2003: Add Trendline" /><img style="margin: 0px auto 10px; display: block; text-align: center; cursor: pointer;" src="http://bp3.blogger.com/_1XYQfEGGEIw/R2U70ewrZeI/AAAAAAAAAAk/iKOpO4y5ePg/s320/excel2003_add_trendline2.png" alt="Excel 2003" /></li>
</ol>
<h3>Tidbits</h3>
<p>This feature was originally requested in 2002 as issue <a rel="external nofollow" href="http://qa.openoffice.org/issues/show_bug.cgi?id=7998">7998</a>. With 72 votes, it is the second-highest rated enhancement or feature request scheduled for OpenOffice.org version 2.4. Issue 7998 is also fairly high on <a rel="external nofollow" href="http://qa.openoffice.org/issues/buglist.cgi?resort=1&Submit%20query=Submit%20query&issue_status=NEW&issue_status=STARTED&issue_status=REOPENED&email1=&emailtype1=exact&emailassigned_to1=1&email2=&emailtype2=exact&emailreporter2=1&issueidtype=include&issue_id=&changedin=&votes=10&chfieldfrom=&chfieldto=Now&chfieldvalue=&short_desc=&short_desc_type=substring&long_desc=&long_desc_type=substring&issue_file_loc=&issue_file_loc_type=substring&status_whiteboard=&status_whiteboard_type=substring&keywords=&keywords_type=anytokens&field0-0-0=noop&type0-0-0=noop&value0-0-0=&order=issues.votes%20desc%2C%20issues.priority%2C%20issues.issue_type">the list of all unresolved issues by number of votes</a>.</p>
<p>OpenDocument 1.1 is currently the latest approved version, but <a rel="external nofollow" href="http://specs.openoffice.org/chart/DisplayTrendLineEquations.odt">the feature specification</a> reveals the use of ODF 1.2. ODF 1.2 is scheduled for <a href="http://www.oooninja.com/search/label/openoffice.org%203.0">OpenOffice.org 3.0</a>.</p>
<h3>Related articles</h3>
<ul>
<li><a href="/search/label/openoffice.org%202.4">OpenOffice.org 2.4 features</a></li>
<li><a href="http://www.oooninja.com/2008/03/data-label-options-calc-chart.html">Six new data label options for charts</a></li>
<li><a href="http://www.oooninja.com/2008/02/reverse-axis-chart-bar-graph.html">Reverse axis in charts</a></li>
<li><a href="http://www.oooninja.com/2008/01/show-bars-side-by-side-double-column.html">Show bars side by side (double column chart)</a></li>
</ul><div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/8544609315733972726-570947794677936340?l=www.oooninja.com'/></div>Andrew Zhttp://www.blogger.com/profile/10108637160465346326noreply@blogger.com2