IBM Lotus Symphony


Trend Lines

Regression curves, also known as trend lines, can be added to all 2D chart types except for Pie and Stock charts.

To access this command...

Select Create > Trend Lines (Charts)

Mean Value Line

Mean Value Lines are special trend lines that show the mean value. Use Create > Mean Value Lines to insert mean value lines for all data series.

Note Icon If you insert a trend line to a chart type that uses categories, like Line or Column, then the numbers 1, 2, 3, ... are used as x-values to calculate the trend line.
Note Icon A trend line is shown in the legend automatically.

When the chart is in edit mode, Lotus® Symphony™ gives you the equation of the trend line and the correlation coefficient R². Click on the trend line to see the information in the status bar.

Show equation

To show the trend line equation, select the Show equation check box in Trend Lines dialog box.

Show correlation coefficient (R²)

To show the correlation coefficient, select the Show correlation coefficient (R²) check box in Trend Lines dialog box.

Note Icon For a category chart (for example a line chart), the regression information is calculated using numbers 1, 2, 3, ... as x-values. This is also true if your data series uses other numbers as names for the x-values. For such charts the XY chart type might be more suitable.

You can also calculate the parameters using Lotus Symphony Spreadsheets functions as follows.

The linear regression equation

The linear regression follows the equation y=m*x+b.

m = SLOPE(Data_Y;Data_X)

b = INTERCEPT(Data_Y ;Data_X)

Calculate the coefficient of determination by

r² = RSQ(Data_Y;Data_X)

Besides m, b and r² the array function LINEST provides additional statistics for a regression analysis.

The logarithm regression equation

The logarithm regression follows the equation y=a*ln(x)+b.

a = SLOPE(Data_Y;LN(Data_X))

b = INTERCEPT(Data_Y ;LN(Data_X))

r² = RSQ(Data_Y;LN(Data_X))

The exponential regression equation

For exponential regression curves a transformation to a linear model takes place. The optimal curve fitting is related to the linear model and the results are interpreted accordingly.

The exponential regression follows the equation y=b*exp(a*x) or y=b*m^x, which is transformed to ln(y)=ln(b)+a*x or ln(y)=ln(b)+ln(m)*x respectively.

a = SLOPE(LN(Data_Y);Data_X)

The variables for the second variation are calculated as follows:

m = EXP(SLOPE(LN(Data_Y);Data_X))

b = EXP(INTERCEPT(LN(Data_Y);Data_X))

Calculate the coefficient of determination by

r² = RSQ(LN(Data_Y);Data_X)

Besides m, b and r² the array function LOGEST provides additional statistics for a regression analysis.

The power regression equation

For power regression curves a transformation to a linear model takes place. The power regression follows the equation y=b*x^a , which is transformed to ln(y)=ln(b)+a*ln(x).

a = SLOPE(LN(Data_Y);LN(Data_X))

b = EXP(INTERCEPT(LN(Data_Y);LN(Data_X))

r² = RSQ(LN(Data_Y);LN(Data_X))

Constraints

The calculation of the trend line considers only data pairs with the following values:

You should transform your data accordingly; it is best to work on a copy of the original data and transform the copied data.

The polynomial regression equation

A polynomial regression curve cannot be added automatically. You must calculate this curve manually.

Create a table with the columns x, x², x³, ... , xn, y up to the desired degree n.

Use the formula =LINEST(Data_Y,Data_X) with the complete range x to xn (without headings) as Data_X.

The first row of the LINEST output contains the coefficients of the regression polynomial, with the coefficient of xn at the leftmost position.

The first element of the third row of the LINEST output is the value of r². See the LINEST function for details on proper use and an explanation of the other output parameters.


Product Feedback | Additional Documentation | Trademarks