Function Problems

Fix Excel LINEST Incorrect Results for 6th-Degree Trendline

Maira Mehtab Sep 22, 2026 872 views

Question details

The user needs to correct the LINEST function output for a 6th-degree polynomial trendline, as the results differ significantly from the chart equation.

Product: Excel 365
Device & OS: not provided
Scenario: Calculating high-degree polynomial regression coefficients using the LINEST function in a spreadsheet.
Observed behavior: Excel visually fits a 6th-degree polynomial trendline, but the displayed equation and LINEST results are incorrect, whereas 5th-degree and lower calculations work correctly.

Before you start

Verify that your dataset contains an adequate number of data points for a 6th-degree regression and that there are no blank cells or text values in your numerical range.

Solution 1Recommended

Rescale or Normalize the X Values

Normalizing X values prevents numerical instability caused by large or poorly scaled numbers in high-degree polynomial regressions.

High-degree polynomial regressions like a 6th-degree trendline can easily become numerically unstable, especially when the input X values are very large. Normalizing the data shifts the values closer to zero, which significantly improves the accuracy of the LINEST function.

Create a normalized column

Insert a new column next to your raw X values to hold the normalized data.

Apply normalization formula

Calculate the normalized values using the formula =(X - AVERAGE(X_range)) / STDEV(X_range) and drag it down to fill the column.

Update LINEST function

Rewrite your LINEST function to reference the newly created normalized X values array instead of the original raw data.

Convert coefficients

If necessary, apply standard mathematical conversions to translate the resulting coefficients back to the scale of your original raw data.

Solution 2

Use a Lower-Degree Polynomial or Alternative Model

Applying a lower-degree polynomial or a different mathematical model provides a more mathematically stable curve fit for highly curved datasets.

Calculate Regressions Accurately with WPS Spreadsheets

WPS Spreadsheets provides highly compatible statistical functions, allowing you to seamlessly process complex polynomial regressions, execute array formulas, and plot accurate trendlines just as you would in Excel.

1. Open your data file: Launch WPS Spreadsheets and open the workbook containing your regression dataset.
2. Select array output range: Highlight a blank range of cells (e.g., 1 row by 7 columns) to output the multiple coefficients required for a 6th-degree polynomial.
3. Input the LINEST formula: Type your regression formula, for example =LINEST(Y_range, X_range^{1,2,3,4,5,6}, TRUE, TRUE) into the formula bar.
4. Execute as an array: Press Ctrl + Shift + Enter simultaneously to execute the formula as an array. WPS will compute and display the accurate regression coefficients.

Fully compatible with Microsoft Excel's LINEST and array formulasRobust data processing for large and complex datasetsFamiliar user interface requires no learning curveFree and lightweight alternative to standard Office suites

Frequently Asked Questions

Why does the chart trendline equation differ from LINEST results?

Spreadsheet charts typically use a different underlying algorithm to generate the visual trendline equation compared to the LINEST function. For high-degree polynomials, standard floating-point arithmetic can lead to precision discrepancies, causing the displayed chart equation to differ from the actual function output.

Can I increase the precision of the chart trendline equation?

Yes. Right-click the trendline equation label on your chart, select 'Format Trendline Label', and change the 'Category' under 'Number' to 'Scientific' or 'Number' with a higher decimal place count (e.g., 15 decimal places).

What is the maximum polynomial trendline degree supported?

Most modern spreadsheet applications, including Excel and WPS Spreadsheets, support up to a 6th-degree polynomial trendline in charts. However, calculations above a 3rd or 4th degree are highly susceptible to mathematical instability if data isn't properly scaled.

How do I enter a polynomial array in the LINEST function?

You can calculate a polynomial regression by adding an array constant to the known_x's argument. For example, for a 3rd-degree polynomial, use the syntax =LINEST(B2:B10, A2:A10^{1,2,3}) and confirm it as an array formula using Ctrl+Shift+Enter.

Excel & Data Problems