Issue 103826

Summary: Utilization of cubic spline function
Product: Calc Reporter: mark_e <mark.ebner>
Component: programmingAssignee: AOO issues mailing list <issues>
Status: CONFIRMED --- QA Contact:
Severity: Trivial    
Priority: P3 CC: issues, rb.henschel
Version: OOo 1.0.0Keywords: oooqa
Target Milestone: ---   
Hardware: Unknown   
OS: All   
Issue Type: FEATURE Latest Confirmation in: ---
Developer Difficulty: ---
Description Flags
odt-file with function descrition and ods-file with spline macro none

Description mark_e 2009-07-27 10:32:12 UTC

The OpenOffice Calc chart wizard contains the possibility to interpolate values
with a cubic spline. For non obvious reasons this function cannot be used in the
spreadsheet. There are OpenOffice Basic macros available which can be added, but
they have some limitations and disadvantages (slow, restricted to columns,
etc.). Therefore, it would be helpful to have this already implemented function
accessible as an array function, e.g.

CUBICSPLINE(known_x's; known_y's; new_x's)

known_x's	is a set of known, not equally spaced x-values.
known_y's	is a set of known y-values.
new_x's		are the new x-values for which CUBICSPLINE returns the corresponding

Thank you.

Comment 1 Regina Henschel 2009-07-27 11:31:10 UTC
I think, that it is not possible for xy-charts in this general way, because the
relation between x and y might not be a function. Think of spirals or ellipses,
which are also drawn with splines.
For a line chart (category chart) it might be possible, but there it is not
useful, because the x values are not those, which are used as axis label, but
always 1,2,3,...
Comment 2 mark_e 2009-07-27 15:42:37 UTC
Created attachment 63803 [details]
odt-file with function descrition and ods-file with spline macro
Comment 3 mark_e 2009-07-27 15:47:30 UTC

there seems to be a misunderstanding. The cubic spline algorithm is already
implemented in OpenOffice Calc. Its results can be displayed in line charts.
However, there is no possibility (yet) to access these calculated values.
Instead of using an additional macro in the spreadsheet (for the reasons
mentioned earlier), I would like to have access to the numerical results of the
function which is already there (see attachments for clarification)

Comment 4 Regina Henschel 2009-07-27 17:30:13 UTC
Hi Mark,

does it mean, you are not speaking of getting a point of the line in the chart,
but you want a new function?

Comment 5 mark_e 2009-07-28 07:59:19 UTC
Hi Regina,

I would like to use nonlinear interpolated values for further calculations. So
far, I can only see them in an OpenOffice Calc chart, but I cannot make any use
of them in a spreadsheet. Maybe I miss something, but the only way I see how
this can be done, is with a new function. I am aware of the fact that OpenOffice
Calc as well as MS Excel include a function called TREND. But this kind of
linear interpolation is not suited for many problems. Since OpenOffice Calc
already includes a cubic spline algorithm for nonlinear interpolation, why not
providing it to the users? Almost everything is already implemented. If it is
done correctly, it is possible to get as many nonlinear interpolated values as
desired with a single function call! That is the beauty of it. 

Comment 6 Regina Henschel 2009-07-28 11:34:46 UTC
Ok, that is clear now. I have seen a SPLINE function in the "huge group" in a
comment in the ODF 1.2 draft. Unfortunately, there are no syntax description of
that function. Does anyone know a spreadsheet application that has implemented
it already?

The solution in the chart cannot be used directly in calc, because it is a local
class in that file. But it can be used as master to copy it.