I would also greatly appreciate direct access to the graph’s trendline equation(s). As I commented at https://bugs.documentfoundation.org/show_bug.cgi?id=112464 this would be very useful because graphs calculate trendlines even if data points are missing in its input - much in contrast to Calc’s regression functions which return ERROR if there are missing values in the input data.
Try to use LINEST() or LOGEST() array function to calculate the coefficients of the equation independently from the Graph.
Thank you for the suggestion. These regression functions, however, return ERROR if any of the values in the input or output matrices are empty/missing. The graph trendlines are calculated without ERROR if there are missing values, hence my question.
Please upload your .ods type sample file here.
Here is an example ods. A single “y” values has been deleted with the result that all TREND and REGRESSION calcs return “Err:502” while the Trendline calculation in the graph calculates without error.
If the empty “y” cell is filled then the TREND and REGRESSION calculations are automatically recalculated without error.
My real life data has measurement sets, which often contain individual values which are missing. Hence the great desirability of having access to the graphs’s trendline equation. Of course, it would be much better if LO’s statistical formula e.g. LINEST/TREND/REGRESSION, could handle such real life data but I imagine that this is much more difficult than providing access to an existing result, which just needs to be made accessible to users.
Data_values_www_#2.ods (24.4 KB)
You can use the function LINEST() with reworked helper cell ranges and with an offset value.
The non existing values must be placed to the end of the input range of the LINEST().
And the ranges of the LINEST() must be calculated based on the count of the valid measuring values.
I only modified the ranges appeared with colored header in you sample file:
Data_values_www_Zizi64.ods (19.2 KB)
Thank you for the suggestion. While this works for the simple, example worksheet, unfortunately, it’s not practical for the 3 different data sets I work on. The simplest is 13 columns by currently 150 rows and growing by 1 or 2 lines daily i.e. approx. 400 lines per year. The largest has several hundred thousand data sets. In all my 3 cases the missing values are distributed randomly throughout the matrices making sorting much less practical.
Statistical functions analysing the data set as they actually occur i.e. with missing values, is highly desirable. Cleaning the data can be complicated enough - without sorting these large data sets for missing values.
Since the graphs’ trendline calcs already handle missing values then it seems the simplest solution is to enable access to the trendline equations e.g. by returning the coefficients as a column of coefficients.
Another tip:
Install one of excellent object inspection tools: XrayTool or MRI. Then (maybe) you will able to check if these values are available by a macro function or not…
Example for handling trend lines by macro:
https://forum.openoffice.org/en/forum/viewtopic.php?p=363184