Difference in calculated (logarithmic) R^2 (Excel)

[Max_B00]

Hi,

When I plot a scatter diagram and add a logarithmic trend line, I get a R^2-value of 0,9596. I was told to convert all data by using log10 and to run linear regression on these values as well. However, the R^2 value is much lower now: 0,8438. Can somebody tell me what went wrong (or what I did wrong)? 

Thanks in advance!

Max

[#2]

@Max_B00  wrote:  ``Can somebody tell me what went wrong (or what I did wrong)?``

Probably not without details.  To paraphrase Uncle Ben's advice to Spider-Man:  "With great details comes great answers". (wink)

Attach an Excel file that duplicates the problem, or provide a download link to the Excel file.  If the forum does not permit you to post a bona fide URL, spell out part of it.  For example, the link to this discussion is techcommunity dot microsoft dot com /t5/excel/difference-in-calculated-logarithmic-r-2-excel/m-p/3789518#M187659 .

[#3]

PS.... @Max_B00 

When you say ``convert all data by using log10 and to run linear regression on these values``, do you mean using LINEST?

I can tell you that the LINEST r^2 will differ from the log trendline R^2 for two reasons.

1. The LINEST r^2 is based on the correlation between log y and x (or log x, depending missing details), whereas the trendline R^2 is based on the correlation between est y and y.

The latter is the correct correlation.  But we cannot blame LINEST, since it only knows the linear relationship that we present it.

2. LINEST r^2 is the coefficient of determination, whereas the trendline R^2 is the squared Pearson correlation coefficient, at least in some versions of Excel.  See the LINEST and RSQ function help pages for the difference.

Usually, their results are the same, within binary arithmetic limitations.

But the difference can be significant if you force a zero intercept.

IIRC, this was corrected after some build of Office 365 and after Excel 2016.

Besides, this should not be a factor in your case.  But again, that depends on details that you omitted.