• Status: Solved
• Priority: Medium
• Security: Public
• Views: 1011

# r2 Formula for polynomial trendlines.

Hi Experts,

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

=CORREL(rangeA,rangeB)^2

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

Thanks!
0
JohnPablo
1 Solution

Commented:
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:  http://spreadsheetpage.com/index.php/tip/chart_trendline_formulas/ and a tip on top of that tip:  http://www.excelbanter.com/showthread.php?t=152118

You should find these links useful as well:

R-squared for nth polynomial - a robust discussion with several example spreadsheets being bandied:  http://www.talkstats.com/showthread.php/7686-R-squared-nth-polynomials-in-excel

A good example and also a textbook link discussing the generation of R-squared on polynomials:  http://www.pcreview.co.uk/forums/need-furmula-r-squared-polynomial-regression-t3324598.html

0

Author Commented:
Thanks a lot, this is a good starting point for my solution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.