How to generate arrays consisting of non-consecutive columns, for use in LINEST (or other array functions)

Oregano · May 25, 2022, 7:30am

My end goal is to use LINEST to generate curve fits using rational functions (i.e. polynomial divided by polynomial).

(I assume hereafter that the independent variable x is located in column B, and the dependent y is located in column A.)

I know that if I had wanted a polynomial curve fit (let’s say of degree 6), I could have entered that quite easily as =LINEST(A2:A100, B2:B100^{1,2,3,4,5,6}) without doing any intermediary calculations.

I also know that one (workaround-ish) way of achieving the rational curve fit would be to generate, from the columns representing x and y, also the columns x^2, x^3, … and yx, yx^2, y*x^3, … and use the formula =LINEST(A2:A100, B2:J100) (or however many columns I needed to precalculate)

My question is then: Is there a way to express, in such a way that Calc understands it, that the “independent data” for the purposes of the curve fit consists not only of the array B2:B100^{1,2,3,4,5}, but also of the array A2:A100*B2:B100^{1,2,3}?
(Assuming I want a 5th order polynomial divided by a 3rd order polynomial.)

That is, how do I merge B2:B100^{1,2,3,4,5} and A2:A100*B2:B100^{1,2,3} into one array that I can feed to LINEST?
(If this is not possible, well, then I’ll add the needed auxiliary columns explicitly. I’m mostly wondering if I can do this in a neater manner.
My testing indicated that it didn’t work to give the two “independent variables” arrays to LINEST separated by comma, or semicolon; nor could I gather the two arrays with braces or brackets or square brackets. I haven’t seen any sign that CONCAT or CONCATENATE would work with arrays, but I haven’t tested that extensively. Beyond that, I am out of ideas.)

Thanks, and sorry for the length of the background section.

[The version of the software is 7.2.6.2, Gentoo Official.]

Lupp · May 29, 2022, 2:53pm

You surely consulted the help and the Calc guide, and found that LINEST() can’t do it for you. It only can accept a single range for all the primary variables (“x-variables”).
You need to either rearrange your columns for ingoing values manually, or use a macro for the purpose.
The attached example is containing such a macro (simple case). It’s based on a feature only available under Option VBAsupport 1, and therefore not running in very old versions of LibreOffice or in AOO.
On the other hand you can use the macro, of course, in different situations with no relation to LINEST(). Since it needs to treat the elements on by one, it will not be very efficient.
rearrangeColumnsDemo.ods (29.5 KB)

Oregano · May 31, 2022, 4:01am

Okay, then I need to learn to write macros, or see what I can come up with in numpy.
Moving columns any time I want to tweak the model is not going to be practical. (5th order over 3rd order? 6th order over 5th order? What about polynomial? Exponential? In short, which model has the better fit over hundreds of data series?)

I did notice that the help entry for LINEST does not mention much about array operations. I do not, from that, draw the conclusion that an “array merge” or “array concatenate” operation would be impossible by using some other function to concatenate the incoming arrays. I understand now from your comment that no such function exists in Calc, though.

Lupp · May 31, 2022, 12:34pm

I’m not a native speaker of any “English” slang.

doesn’t make much sense to me. If you mean a quotient of polynomial ,functions in both cases (first line), you will need to study basically how rational functions migth be used to get a fitting for … what?.. Empirical data?
I don’t know a mathematical theory actually applicable to such an attempt, and LINEST() surely can’t do it for you because it’s concept of “good fitting” is based on minimizing squaresums. Did you consider the roots of the denominators?
Begin with the beginning.

What I addressd in my answer was the question you posted as the subject of the thread.
I dont know the “do-what-I-have-in-mind-function” though it’s often requested.