Looking for user experience: improve trend line

[jumbo444]

Hello,

I am looking for help about user experience with polynomial regression curve, which is one of the most wanted feature in Chart, cf. issue 20819. We need file format modification prior been able to solve this issue. First of all, we have to ask for such a demand to OASIS TC.
A draft has been already written for a file format change in ODF. It needs to be modified on two points:
- argue about importance of this feature, so that it will more likely succeed
- list the most important missing features with trend lines

For the first point, we need to gather concrete examples of use of polynomial trend lines, in several fields, not only in maths and physics: economy, biology, history, geography, etc. Another argument to put forward, is that this functionality exists in other spreadsheets since more or less long time: for MS-Excel at least since Excel 97, probably Excel 95, I did not already check. Gnumeric does it? Since when? Thank you for transmitting informations about this subject.

For the second point, it is obvious that asking a file format change only for polynomial regression curve is unproductive. That's why the proposition includes other important issues for regression curve:
- moving average trend line (issue 66819)
- force intercept (issue 34093)
- extrapolate (issue 5085)
Do you see other features that are missing to trend lines? I made comparison with only MS-Excel, but other spreadsheets may have interesting features.

With this four first features, there are things that should be discussed before asking to OASIS TC a file format change:
- polynomial trend line: Laurent Godard had included in his macro CorelPoly the possibility to use exponential terms: y = sum a_i. exp (i. x) instead of y = sum a_i. x^i. Which applications do you see such expression have? Is it necessary to add this option?
- moving average trend line: by default it is prior moving average. What about adding central moving average? Is it necessary, or will there be too many parameters, most of them useless?

Many thanks in advance for your answers. I've attached to this message the documents under works. Please comment them.

[Dave]

For the first point, we need to gather concrete examples of use of polynomial trend lines, in several fields, not only in maths and physics: economy, biology, history, geography, etc.

Is there not mathematical software already available and more dedicated for this purpose?

David.

[jumbo444]

Is there not mathematical software already available and more dedicated for this purpose?

Sure, for instance R Statistics will do it. But other spreadsheets (MS-Excel, Gnumeric) already do it. So we need it at least for compatible problems.

[privatne]

Hello, does it mean that moving average is not yet available in version 3? When I opened an Excel graph with that function, the trend line has disappeared. And, I could nor find this function in Calc. Thanks.

[preston48118]

My need of the polynomial trend line is to estimate the Population of the U.S. based on census data from 1790-2000 and extrapolate it to 2040. The polynomial trend line has an R-squared value of .9996 and is useful for this extrapolation. The linear (.92), log (.63), and power (.94) options are just not good enough to extrapolate.

[Villeroy]

http://extensions.services.openoffice.o ... ect/R4Calc

This extension allows one to use the open source R statistics package within OpenOffice Calc. It comes with a number of features, including new user interface windows for using advanced statistical tools, new spreadsheet functions, and a way to use R scripts within Calc.

[Solved+Issue] Polynomial regression

[miche]

Hi all,

I'm looking for a way to display exponential trendline equation in the form y=b*exp^a*x (which is the form of Excel) rather than OO default y=b*m^x. Am I missing something?
Thanks,
Miche

[FJCC]

I don't think you can force the display of the equation in the form y = b*exp^(a*x) but the information is there in the equation Calc provides. The form y = b*exp^(a*x) can also be expressed as y = b*(exp^a)^x. Comparing to the Calc form y = b*m^x, you can see that m = exp^a. So, to get the constant a from the Calc form you would use a = Ln(m). You can then create a text box with the equation and place it wherever you want on the graph.

[miche]

Thanks for your fast reply. I was looking for the "excel" displayed for just for the sake of practicality. I have a large number of graphs and the information I want to show is the "a" which I actually calculate as you indicated. This means manual curation of each graph.
Thank you for your help.
Best,
M

[FJCC]

I think you can save some manual work using the LOGEST function. If you enter it in the form

Code: Select all

=Logest(YData;XData;1;1)

and don't enter it as an array function (i.e. just hit enter, not CTRL+SHIFT+ENTER), it will return the m value of the fit y = b*m^b. You can then use another cell to compute the LN of that value or bundle it all in one cell as

Code: Select all

=LN(LOGEST(YData;XData;1;1))

That will give you your a constant for each data set. If your data sets are all the same size you can just copy and paste that formula in a convenient place near each data set. If the sets are different sizes, you can paste the formula and then adjust the X and Y data sets by clicking on the cell with the formula, pressing F2 and dragging the sizes of the red and blue boxes that will appear around the X and Y data ranges.

[miche]

Thanks FJCC, I will try to save some manual work using LOGEST. Anyway either ways are fine, it is just a matter of notation and not substance. Thanks for your help.
Miche

[miche]

On more question: do I have a way (i.e.: macro?) to change all the titles of a series of charts with constant text? I'm not very inside macros world..
Basically I'm copying a sheet to another and changing only the data source, but all the operations and charts are the same. But then I have to change all the titles according to the new informations inserted.

Many thanks!

[FJCC]

You can use a fairly simple macro to change the title of every chart in a sheet. This code asks for the new title and then changes the title of all the charts on the currently active sheet.

Code: Select all

NewTitle = InputBox("Enter new title", "Title Entry")
oDoc = ThisComponent  'Get the current document
Sheet = oDoc.CurrentController.ActiveSheet 'Get the current sheet
Charts = Sheet.getCharts 'Get the collection of charts
ChartEnum = Charts.createEnumeration 'An Enumeration allows stepping sequentially through the collection
While ChartEnum.hasMoreElements
	PickedChart = ChartEnum.nextElement
	ChartObj = PickedChart.EmbeddedObject
	TitleObj = ChartObj.Title
	TitleObj.String = NewTitle
Wend