« Regression analysis I : Basic linear formulas | Main | Regression Analysis III : LINEST »
April 13, 2005
Regression Analysis II : Basic functions, charting
In a continuation from the previous tip, we look at some basic built-in functions for determining trendline coefficients of a X-Y plot.For a series of X-Y values that we suspect have a linear relationship, we can determine the slope and y-intercept values of the linear approximation using the builtin functions SLOPE() and INTERCEPT as showb below. Compare these values to those we obtained when performig our regression analysis without using the builtin functions.
Now, plotting X-Y data is straightforward. Adding a trendline takes a little more work. We start off with the basic X-Y plot.
First, select the chart for editing by right-clicking anywhere inside the chart boundary. Now, before we add the trendline, we need to select the data series for editing. Move the cursor over one of the data points - you will see popup info about the nearest data point.
Left-click and the data series is now selected (below)
Now right-click and select Object Properties (below)
The Data Series dialog opens up. Select the Statistics tab (below). Select the Linear Regression curve and click OK
The modified X-Y plot with the newly added trend line.
Posted by Dave at April 13, 2005 02:58 AM
Comments
well done, how can I get the equation of the regression curve. would be very important to calculate correlations.
Cheers for your help.
Posted by: juergen at October 18, 2005 11:43 PM
Thanks, very helpful.
Posted by: Anonymous at October 26, 2005 07:50 AM
Adding a trend-line without the possibility to know its equation is almost useless.
It has been a problem in Calc 1.x for years and it still is a problem in Calc 2.0.
The whole chart module has to be re-organized if Calc wants to compete with other spreadsheets. It should be possible, for example, to plot XY data series with different X sets.
Regards
Posted by: Alberto at January 22, 2006 03:21 PM
Nice explanation; thanks.
I find Calc charts to be almost on par with Excel's. However, that's not saying much. Calc needs to be able to make graphs like histograms, and deal with irregular x-axis intervals. Since Excel doesn't do that, it would be a great feature to set them apart, too.
Posted by: a different Anonymous at February 20, 2006 08:47 PM
> Calc needs to be able to make graphs like histograms, and deal with irregular x-axis intervals.
For irregular x intervals just use the X-Y plot type and put x values in one column and y values in another.
Posted by: Michał Kosmulski at March 21, 2006 02:54 PM
Great tip, thanks.
Posted by: Brendon at March 26, 2006 03:05 AM