GNOME Bugzilla – Bug 680510
interpolation function should work with decreasing abscissas
Last modified: 2013-03-21 13:39:05 UTC
- open the attached .gnumeric file - cell D1 displays #Value! - switch cells A1 and A2 - cell D1 now displays 1
Created attachment 219562 [details] .gnumeric file to reproduce the problem
Created attachment 219727 [details] [review] proposed patch
This problem has been fixed in the development version. The fix will be available in the next major software release. Thank you for your bug report.
The patch does actually more than just decreasing abscissas. It does random abscissas by re-ordering them.
I know. That was intended.
There are certainly some value to propose an automatic re-ordering but it might be dangerous too. In the case when there are identical abscissas, gnumeric now report an error but it would be more helpful to make an average of the ordinates with the same abscissas. (in data analysis, it is quite common to have duplicate measurements and in this case the common procedure is to take the average) Other remarks: - gnumeric reports an error when one of the data (abscissa or ordinate) is an error (#NUM!, #DIV/0!, #VALUE!). It would be more helpful to skip these data, as is done for the XY plots. - Linear Interpolation of (1;2;0) (2;3;2) in 3 gives 3.5. It should give 4. (the re-ordering affects the result)
If you have multiple data points with the same "x" value, you are using the wrong tool. Interpolation is a connect-data-with-some-curve game. What you describe calls for best-fit-within-function-class. That kind of thing is available, for example via functions like LINEST.
> - Linear Interpolation of (1;2;0) (2;3;2) in 3 gives 3.5. It should give 4. > (the re-ordering affects the result) Why? to get 4, it would have to be (2;3;1).
Morten, =interpolation(array(1,2,0),array(2,3,2),3) and =interpolation(array(0,1,2),array(2,2,3),3) should be the same, but aren't, they are 3.5 and 4 respectively. since for values outside the range we are extrapolating based on the last 2 values (on that side), 4 would be correct.
The 3.5 vs 4 was a stupid coding error on my part. This problem has been fixed in the development version. The fix will be available in the next major software release. Thank you for your bug report.
Hi Andreas, So how would you proceed if you had a serie (X_i,Y_i) and wanted to eliminate the duplicate X_i by taking the average of the corresponding Y_i?
Frederic, First of all I would ask myself why I have duplicate X_i. In the case of duplicate X_i, I either don't have a function I could interpolate or my Y_i are error-laden and interpolation may be inappropriate, but a statistical model may be much more appropriate.
OK, thank you for your fix BTW.
Sorry to come back here, it still seems valuable to have the interpolation function taking the average of the Y_i for duplicate X_i. I will try to propose a patch.
After more thoughts and uses, I find it too dangerous to re-order the series before doing the interpolation. It prevents you from finding errors in your code. I would advocate to do the interpolation only for strictly increasing or strictly decreasing abscissas.