Excel Trendline Formula Errors

[anonymous321]

Hi,
I have data in excel attachment. I plotted that data and created linear trendline. Now when I use the formula to calculate values of y using the same trendline data points then it gives me wrong values. I have attached the file and created the comment in the file for your understanding. Please advise on this issue.

Thanks and Regards,

[AliGW]

What would the correct results be and why?

Administrative Note:

Is your forum profile showing the Excel PRODUCT that you need this to work for?

Members will tailor the solutions they offer to the Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your product is for Mac, please also state this.

The three most recent Excel products are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the version number in your profile (e.g. MS365 Version 2306). This is in the About Excel section further down the Account page.

Thanks.

[anonymous321]

The values are negative, as you can see from the trendline. The formula is not giving right results based on trendline.

[AliGW]

And what would the correct results be?

[Glenn Kennedy]

The slope is inaccurate from the graph. USE SLOP and INTERCEPT and respect the formula y=mx+c and it's fine.

[anonymous321]

Thank you so much Glenn. Now my concern is , what if the graph is not a straight line, and we only rely on Trendlines to produce the equation based on given data sets. These automatically produced equation will not be right to plot and predict values for future. I am keen to know about it.

[Glenn Kennedy]

For a 4th order polynomial:

=LINEST(known ys,known xs^{1,2,3,4})

Cell 1 = 4th order multiplier
Cell 2 = 3rd order multiplier
Cell 3 = 2nd order multiplier
Cell 4 = 1st order multiplier
Cell 5 = Intercept


For a 3rd order polynomial:

=LINEST(known ys,known xs^{1,2,3})

Cell 1 = 3rd order multiplier
Cell 2 = 2nd order multiplier
Cell 3 = 1st order multiplier
Cell 4 = Intercept

LINEST needs to be entered as an array formula in ant Pre-O365 product (CTRL-SHIFT-ENTER). Following taht pattern, you can solve any order polynomial.

Here's a 3rd order example.

You're welcome. Thanks for letting us know that you got an answer.

---

Please take a moment and consider clicking the "Add Reputation" button at the foot of any of the posts of anyone who helped you reach a solution here today.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.