[Solved] Estimate differentials with exponential function

[zap]

I have a column with rising values. Say, column A is like:
123
134
156
196
215
268
...
Now I want to estimate these numbers with a exponential function y = a^x.
If I use just LOGEST(A1:A6;;0) it will compute a.
But I would like to fit the y as if it would be relative to first value, e.g:

123-123
134-123
156-123
196-123
...

How I can compute LOGTEST without creating a separate row with subtracted values for this?
E.g. something like LOGEST(A1:A6-A1;;0) but this doesn't work.
Does Calc have functions that output matrices that can be used as arguments to other functions at all?

[FJCC]

Ones reason that

Code: Select all

=LOGEST(A1:A6-A1;;0)

does not work is that there is no solution to 0 = a^x. You can try

Code: Select all

=LOGEST(A2:A6-$A$1;;0)

entered as an array formula, that is, confirmed with CTRL + Shift + Enter instead of a plain Enter.

[MrProgrammer]

If I use just LOGEST(A1:A6;;0) it will compute a. But I would like to fit the y as if it would be relative to first value

To use LOGEST, all of the Y values must be positive, since Calc will need to calculate their logarithms. Thus you cannot subtract 123 since 123-123 is zero. However if you want to look at relative values you could divide all the values by 123. The formula would be =LOGEST(A1:A6/A1;;0). This is an array formula so type it and and press ⇪⌘Enter (Shift+Command+Enter) on a Mac or Ctrl+Shift+Enter on other platforms; if you press Enter by mistake, use Edit → Delete Contents → Formulas, then try again.

If you need any additional assistance, attach a document demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself). Explain what results you expect, that is, your goal.

Does Calc have functions that output matrices that can be used as arguments to other functions at all?

Yes, though we use the term "array" instead of "matrix". See List of Calc Array functions, one of which is LOGEST.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[zap]

Thank you for help! My real problem was that I was subtracting the first term, and indeed there's no log(0).
I was getting "Error 502" which I wrongly interpreted as that the syntax is incorrect, but in reality it was meaning "invalid argument to function".
The link to array formula docs was also very helpful; I searched for it but wasn't able to find it (maybe because I used the term "matrix math" or something like that).

[Lupp]

My real problem was that I was subtracting the first term, and indeed there's no log(0).

I'm not quite sure.
You didn't tell anything about the primary (X-) values.
You didn't tell for what reasons you tryed to find an exponential function with estiamted parameters.
You didn't tell if you actually had only 6 data (pairs?), or in what way you chose the sample...

I was in the mood to play a bit with your data and to make a kind of essay of them.
See yourself:

aoo101633approximation.ods

(94.78 KiB) Downloaded 124 times