• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1011
  • 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
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

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