title: R-squared value in Excel with the 'addtrendline' function?
body: <p>I have been struggling a while on that;
  In short, I can't find the equations Excel uses for R<sup>2</sup>.</p>
  <ul>
  <li><p>Here are my data :<br>
  x: 1 2 3 4 5 6 7 8 9 10<br>
  y: 4 9 1 2 1 1 8 5 5 1 <br></p>
  </li>
  <li><p>I plot the data, fit a power law function ('add trendline') and use the 'add trendline &gt; options &gt; Display R-squared value on chart'</p>
  </li>
  </ul>
  <p>Value displayed : <br>
  <strong>R<sup>2</sup> = 0.03008</strong>.</p>
  <h3>Problem 1 <br></h3>
  <p>If I calculate it in excel using 'RSQ()' function (taking the values of the parameters Excel has found for the fitting function), or by hand using the definition (wikipedia)...<br><strong>R<sup>2</sup> = 0.0272</strong> <br></p>
  <h3>Problem 2 <br></h3>
  <p>In Matlab, using the 'fit' function, the parameters for the fitting function (and then of course the R<sup>2</sup>) are not the ones that EXCEL has found.</p>
  <h3>Questions:</h3>
  <p>So here is my main question :<br>
  How does Excel computes the R<sup>2</sup> in the 'add trendline' function, as it is obviously not the one from the definition (wikipedia)? <br></p>
  <p>and the bonus question: <br>
  Why Excel and Matlab don't end up with the same fitting parameters?</p>
  <p>thanks a lot!</p>
  <p>%%%%%%   EDIT BELOW!    %%%% <br>
  As an answer to a comment;
  Here is the Matlab code I use:</p>
  <pre><code>%% R-squared with the fit function
  % use the fit function in Matlab, yobs being the data
  [param, results] = fit(x,yobs,'power1');   
  
  % R-squared from the fit function :
  r_sq_from_fit = results.rsquare;
  
  %% here I calculate &quot;by hand&quot; the R-squared, from the general definition (wikipedia!)
  % calculates the fitting data yfit
  yfit = (p_powerlaw.a).*x.^p_powerlaw.b;
  
  % mean of the yobs, total sum of squares, and residual sum of squares
  yobs_mean = mean(yobs);
  SStot = sum((yobs-yobs_mean).^2);
  SSres = sum((yobs-yfit).^2);
  
  r_sq_hand = 1-SSres/SStot;
  </code></pre>
  <p>I find the same values wether I get R-squared from the <code>fit</code> function in Matlab or I calculate it &quot;by hand&quot;. Matlab seems to be consistent and apparently uses the strict definition of R-squared in its function...</p>
  <p><strong>However</strong>; when I compare:</p>
  <ul>
  <li>the R-square value given by Excel from <code>RSQ()</code> function</li>
  <li>and the value I obtain by hand calculating R-square from the definition (taking of course the <em>yfit</em> values that Excel returned me, not the one Matlab returns, as Excel and Matlab don't agree on the fitting parameters!)</li>
  </ul>
  <p>...I obtain different values! Excel : 0.027, as I said before, and hand calculated : -0.1109 (!)</p>