more accurate values for polynomial trendline coefficients

Question

Level 1

4 points

more accurate values for polynomial trendline coefficients

I have a dataset of number pairs that do define a quite smooth curve with some gentle curves. Plotted in Numbers, by eye the polynomial trendline appears to be a good fit if it is 5th order. But the displayed equation does not seem to display enough accuracy (digits) to allow me to use them to calculate good values. For example the 5th order coefficient is displayed as "-0.0004", which could be anywhere from -0.000351 to -0.000449. What the actual value of that coefficient is makes a big difference in the outcome, as x values go as high as 365. I have tried using more digits to optimize each coefficient separately, but that's what a least squares fitting code should be doing properly for all of the coefficients simultaneously for me. How can I get more accuracy (digits) in the displayed values of the coefficients of the fitted polynomial?

MacBook Pro 14″, macOS 14.1

Posted on Jan 24, 2025 10:37 PM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

30,366 points

Jan 25, 2025 6:06 AM in response to Allan_in_CA

Here is a short example using LINEST to calculate the precise coefficients. The chart creates the coefficients accurately but it rounds them for display in the equation and there is nothing you can do to change that. If you need to see the precise coefficients on your chart, you can create a one-celled table that creates a string that is the trendline equation and overlay that table on the chart.

Table 1 is the data

Table 2 is the same data but with a column for X^2

E2 =INDEX(LINEST(C,A:B),1)

E3 =INDEX(LINEST(C,A:B),2)

E4 =INDEX(LINEST(C,A:B),3)

If the order of your polynomial is greater you will have additional columns. For a 4th order you will have X on the left then X^4, X^3, and X^2. The range reference in LINEST will be A:D instead of A:B.

Reply

Answer 2

Allan_in_CA Author

Level 1

4 points

Jan 26, 2025 1:14 AM in response to Badunit

Using Numbers 3.1 on MacBook Pro, Sonoma 14.1. Was able to re-create example given by Badunit. And then successfully applied it to one of my needed cases, a 4th order fit. But the in-app description (Functions side-bar) of LINEST does not seem to explicitly or clearly indicate using it for regression any more than first order (linear). The description there implies that more than two columns of x values would be taken as just that, several different sets of x's to use with the y's for several linear regressions.

Although the method given by Badunit above does indeed work nicely, the in-app description should be edited to describe this. Before adopting his technique, I did manage to cobble together a hack solution: I made a copy of the x and y data, but with new x values made of the originals divided by 10. This forced each of the least squares fit polynomial coefficients displayed in the graph to be larger by a factor of 10^n, where n = order of the term. The coefficient of the x^2 term becomes 100 times larger, the x^3 term 1000 times larger, etc. So for this modified x data, the former 5th order term -0.0004 x^5 inadequately displayed on the graph was now displayed as -42.489 x^5. So I now have the needed number of digits in each coefficient string, by dividing them back down by the appropriate power of ten. So the 5th order coefficient for the original x values was also obtained this way as -4.2489E-4.

(Both techniques provided the same better coefficients.).

Reply

Answer 3

Badunit

Level 7

30,366 points

Jan 26, 2025 6:53 AM in response to Allan_in_CA

LINEST calculates coefficients for equations of the form y = m1*x1 + m2*x2 + m3*x3 + b

where

M1, M2, M3... are the coefficients to be calculated

X1, X2, X3... are "independent" x values

You can also use it to calculate coefficients of common non-linear functions by transforming them into linear functions.

y = a*ln(x) + b transforms into y = a*z + b where z = ln(x)

Create a column for ln(x) and use that column in place of your x data

y = a*b^x transforms into ln(y) = ln(b)*x + ln(a)

Create a column for ln(y) and use that in place of your y data

Use EXP to convert the coefficients ln(a) and ln(b) into a and b

Fun stuff

Reply

Answer 4

Allan_in_CA Author

Level 1

4 points

Jan 26, 2025 3:15 PM in response to Badunit

Thanks to Badunit and SGill for useful responses. Will use methods described in future as needed. For other readers’ sakes note that Numbers Chart Series Trendlines tool does also offer the common other nonlinear types (e.g. power, log, exponential). But one may need a change of variables such as Badunit described and then LINEST to get more digits in coeffs. of fit.

Numbers-> functions help = Apple Support online help re LINEST has same text as in-app sidebar. Have sent note to feedback path asking for polynomial fitting description to be added.

Excel allows you to format the fitted equation that is displayed. In Numbers I see one can select the fitted equation displayed in a chart, but even then not do anything, not even just move it.

Reply

Answer 5

SGIII

Level 8

37,550 points

Jan 26, 2025 1:53 AM in response to Allan_in_CA

Just in case you are not aware, this is a user-to-user forum. If you have a request for Apple to change documentation then you might try going to Numbers > Provide Numbers Feedback in your menu.

BTW, the "in-app description" may be easier to read by going the Help > Formulas and Functions Help in your menu.

SG

Reply