Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-09-2022, 05:27 AM
neager543 neager543 is offline Why does Excel seemingly always calculate the wrong R^2 value in graphs? Mac OS X Why does Excel seemingly always calculate the wrong R^2 value in graphs? Office 2016 for Mac
Banned
Why does Excel seemingly always calculate the wrong R^2 value in graphs?
 
Join Date: Apr 2022
Posts: 1
neager543 is on a distinguished road
Default 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?
Reply With Quote
  #2  
Old 04-09-2022, 05:34 AM
p45cal's Avatar
p45cal p45cal is offline Why does Excel seemingly always calculate the wrong R^2 value in graphs? Windows 10 Why does Excel seemingly always calculate the wrong R^2 value in graphs? Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

That depends on how you calculate it…
Reply With Quote
  #3  
Old 04-09-2022, 07:53 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Why does Excel seemingly always calculate the wrong R^2 value in graphs? Windows 7 64bit Why does Excel seemingly always calculate the wrong R^2 value in graphs? Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
  #4  
Old 04-10-2022, 12:10 AM
joeu2004 joeu2004 is offline Why does Excel seemingly always calculate the wrong R^2 value in graphs? Windows 7 32bit Why does Excel seemingly always calculate the wrong R^2 value in graphs? Office 2007
Advanced Beginner
 
Join Date: Aug 2016
Posts: 32
joeu2004 is on a distinguished road
Default

@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.


-----
Attached Images
File Type: jpg exp trendline r2.jpg (239.7 KB, 14 views)

Last edited by joeu2004; 04-10-2022 at 12:45 PM. Reason: minor typos
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Re-creating graphs in Excel blackarrow Excel 0 08-30-2016 03:27 PM
Why does Excel seemingly always calculate the wrong R^2 value in graphs? 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
Why does Excel seemingly always calculate the wrong R^2 value in graphs? 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

Other Forums: Access Forums

All times are GMT -7. The time now is 04:23 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft