Solved

# r2 Formula for polynomial trendlines.

Posted on 2011-05-10
824 Views
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
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:  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 Closing Comment

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

## Featured Post

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…