r2 Formula for polynomial trendlines.

Posted on 2011-05-10
Last Modified: 2012-05-11
Hi Experts,

Given a pair of ranges (for rangeA, rangeB), I am able to calculate the r2 number by using
the following formula:


Now, I need a formula to calculate the r2 for polynomial trendlines.

Question by:JohnPablo
    LVL 41

    Accepted Solution

    You could generate X,Y values from a polynomial equation, you can use the EVALUATE function to derive f(x) = ax^n+bx^n-1+...+c and plot, add a nonlinear trendline which should give you an r-squared...  a bit manual... but, perhaps good for testing the more automated methods you may develop.

    Here's some quick research to give food for thought.  Suggest you try a few approaches before you hone one for your specific needs.

    This link shows how to create equations to get at the coefficients for such trendlines: and a tip on top of that tip:

    You should find these links useful as well:

    R-squared for nth polynomial - a robust discussion with several example spreadsheets being bandied:

    A good example and also a textbook link discussing the generation of R-squared on polynomials:


    Author Closing Comment

    Thanks a lot, this is a good starting point for my solution.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    Outlook Free & Paid Tools
    Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now