[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 952
  • Last Modified:

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
Asked:
JohnPablo
1 Solution
 
dlmilleCommented:
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
 
JohnPabloAuthor Commented:
Thanks a lot, this is a good starting point for my solution.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now