Apache OpenOffice (AOO) Bugzilla – Issue 102859
Trend Lines in scatter plot is wrong
Last modified: 2013-02-24 21:22:44 UTC
I drew a x-y scatter plot and added trend line with linear option and showed equation. It should be y = 10.21x + 83.416 but open office gives 5.5x + 165.32. Also R^2 is different. Excel gives 0.385 but open office gives 0.33. I think it is a bug that should be fixed ASAP because linear trend estimation is very popular and common tool. Please check the problem with following data. y x 115.22 3.69 135.98 4.39 119.34 4.75 114.96 6.03 187.05 12.47 243.92 12.98 267.43 14.2 238.71 14.76 295.94 15.32 317.78 16.39 216 17.35 240.35 17.77 386.57 17.93 261.53 18.43 249.34 18.55 309.87 18.8 345.89 18.81 165.54 19.04 196.98 19.22 395.26 19.93 406.34 20.13 171.92 20.33 303.23 20.37 377.04 20.43 194.35 21.45 213.48 22.52 293.87 22.55 259.61 22.86 323.71 24.2 275.02 24.39 109.71 24.42 359.19 25.2 201.51 25.5 460.36 26.61 447.76 26.7 482.55 27.14 438.29 27.16 587.66 28.62 257.95 29.4 375.73 33.4
Hi, wrong component, it's Chart not Spreadsheet, changed it. Not a bug. If you define the X and Y values in the correct way (X values are by default in the first column) the shown equation and R squared are the same as the ones given by Excel. Frank
closed wfm
I input X and Y in the correct way. Do you think I exchange x and y? Of course, I changed X and Y location and drew xy plot. Previous x and y are my raw data. I correctly set X and Y in chart option and do regression in chart but it gives wrong coefficient and R^2. Did you try to plot and show linear trend and check the equation? Please try it with my data and comment!! OOs gives 5.5x+170.82 Excel gives 10.21x + 83.42 If chart gives Regression specific number of regression coefficient and r^2, i thought it is also a spreadsheet issue. It could be just a matter of chart, but many people including my students run the regression with chart not with SLOPE or INTERCEPT commend. By the way SLOPE and INTERCEPT commend give right answer : slope = 10.21 and intercept is 83.42. But Chart Linear Trend gives wrong equation and wrong R^2!! Please check the issue again.
I have tried your data and I get the equation y=10.21x+83.43 and R²=0.39. There is likely something wrong with your file. Please attach the document, which shows the error. Please make sure, that all numbers are really numbers. OOo does not convert text to numbers automatically as Excel does.
I found why I have problem. When I drew plot, I choose "first column as label". I thought the first column is used as x axis but it is not selected as x-values. But I should not choose the "first column as label" option. Sorry to bother you. It was my mistake.
So I close it.