Calculating 2 x r squared values

[JeffGrant]

Hi All,

I have two trend lines generated from the same data. As you can in the chart, the polynomial trend line creates an R squared value of 1. The linear regression trendline creates an R squared value of 0.148.

My data set is

X	Y
57.5​	16.56​
52​	16.34​
54​	16.76​

Using LINEST(B2:B4,A2:A4^{1,2}), I can get the co-efficients of the polynomial.
Using LINEST(B2:B4,A2:A4), I can get the slope and intercept of the linear trend line.
Using RSQ(B2:B4,A2:A4), I can get the R-Squared value of the linear trend line

How can I get the R-Squared value of the polynomial from the same dataset?

I have tried a few alternative methods to calculate the R Squared and it always calculates the values for the Linear Trend line, not the polynomial.

I really would like to have both R-Squared values in the same sheet, so the I can switch formulas as the data changes.

Thanks in advance

 

[maabadi]

Try this:

Book1
	A	B	C	D	E	F	G	H
1	X	Y
2	57.5	16.56
3	52	16.34
4	54	16.76			1
5
6
Sheet3

Cell Formulas
Range	Formula
E4	E4	=INDEX(LINEST(B2:B4,A2:A4^{1,2},TRUE,TRUE),3,1)

To get R-squared as a cell value for different types of trendlines:

Linear

=RSQ(known_ys, known_xs)

Power

=RSQ(LN(known_ys), LN(known_xs))

Logarithmic

=RSQ(LN(known_ys), known_xs)

Exponential

=RSQ(known_ys,LN(known_xs))

Polynomial (2nd degree)

=INDEX(LINEST(known_ys,known_xs^{1,2},TRUE,TRUE),3,1)

Polynomial (3rd degree)

=INDEX(LINEST(known_ys,known_xs^{1,2,3},TRUE,TRUE),3,1)

 

[JeffGrant]

thanks Maabadi. that's great.

 

[maabadi]

You're Welcome & Thanks for Feedback.