#1
|
|||
|
|||
Why does Excel seemingly always calculate the wrong R^2 value in graphs?
Whenever I calculate the R^2 value for a trendline in excel it always ends up different from the value I got when I calculated it on my TI-Nspire or an online calculator. The equation of the trendline will usually end up different too, any reason to why this is?
|
#2
|
||||
|
||||
That depends on how you calculate it…
|
#3
|
||||
|
||||
As you have not given details perhaps Floating-point arithmetic may give inaccurate result in Excel - Office | Microsoft Docs
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#4
|
|||
|
|||
@neager543... For a quicker useful explanations, post some details. Ideally, attach an Excel file that demonstrates the problem.
But at the very least, post the data, the type of chart that you use, the trendline equation and R^2 that Excel displays, and the trendline equation and R^2 that you expect. Show the values and coefficients with the precision of 15 significant digits. (Note: That might not be the same as 15 decimal places.) ----- Re: ``the R^2 value for a trendline in excel it always ends up different [....] The equation of the trendline will usually end up different too`` One possible explanation might be the precision of the data. That is, the number of decimal places that you enter into the calculator vs Excel. ----- Another possible explanation is: you are using a Line chart instead of an X-Y Scatter chart. The Line chart always uses x = 1, 2, 3 etc, which might not be the actual x-values. ----- That said, it is true that the trendline "R^2" is calculated differently in various versions of Excel, due to recent corrections. 1. In earlier versions of Excel, the trendline "R^2" calculates the square of the Pearson correlation coefficient (R^2). That is what the Excel RSQ function returns. But in recent versions of Excel, the trendline "R^2" calculates the coefficient of determination (r^2). I call it the CoD to avoid confusion. That is what the Excel LINEST function returns in row 3 column 1 when the last parameter is TRUE. Usually, the RSQ and CoD are about the same value. But read the LINEST help page for details about when and how they differ. 2. For the exponential trendline, for example, the exponential formula is y = b * EXP(a*x). The corresponding linear regression formula is ln(y) = ln(b) + a*x. In earlier versions of Excel, the exponential trendline "R^2" calculates the RSQ of the linear regression formula. Thus, it is a measure of correlation between actual ln(y) and estimated ln(y). But in recent versions of Excel, the exponential trendline "R^2" calculates the RSQ (or CoD?) of the exponential formula. Thus, it is a measure of the correlation between actual y and estimated y. The attached image demonstrates these differences. ----- Last edited by joeu2004; 04-10-2022 at 12:45 PM. Reason: minor typos |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Re-creating graphs in Excel | blackarrow | Excel | 0 | 08-30-2016 03:27 PM |
excel graphs in powerpoint | mdarcey | PowerPoint | 2 | 01-24-2012 02:56 PM |
PowerPoint graphs from Excel | Stormy Girl | PowerPoint | 0 | 06-22-2011 05:17 AM |
Linking graphs from excel | Markc | PowerPoint | 2 | 05-11-2010 02:17 AM |
Linked graphs printing wrong color | StaffsLebowski | Word | 0 | 12-15-2009 08:44 AM |