Over several versions, Excel made a series of changes to internal calculations to correct results from chart trendlines where the trendline intercept is set to zero (0). These changes don't actually change the line or appearance, just the calculation of R2 if you include that annotation on the chart. This calculation occurs each time an Excel workbook is opened. Consequently, the same workbook can show different calculations depending on the Excel version used.
This situation applies to data in a chart which is a fixed-length sequence of numbers, plotted as X and Y:
X = { x_1,x_2,…,x_N }
Y = { y_1,y_2,…,y_N }
The trendline of the data is an equation based on the values, that are expressed as Z. To compute R2, the trendline Z values are evaluated at all the same X values:
Z = { z_1,z_2,…,z_N }
For example, if the trendline equation is:
Z(x) = 2*e(4x)
Then the set Z is evaluated at every X value:
Z = { Z(x_1), Z(x_2), …, Z(x_N) }
Where:
sum(y) = Sum from i=1 to N, the value y_i within the set Y.
sum(z2) = Sum from i=1 to N, the value z_i2 within the set Z.
sum2(x)= ( sum(x) )2
ln(x) = The natural logarithm of x
ln2(x) = ( ln(x) )2
Mean(X) = sum(x) / N
Mean(ln(x)) = sum( ln(x) ) / N
Given these two sequences of numbers: Y and Z, Excel computes R2 in the following ways:
Excel versions earlier than 2005 (May 2020)
For polynomial, linear, and logarithmic trendlines:
R2(Z,Y) = ( 2 N sum(yz) - N sum(z2) - sum2(y) ) / ( N sum(y2) - sum2(y) )
For exponential and power trendlines:
R2(Z,Y) = ( 2 N sum(ln(y) ln(z)) - N sum(ln2(z)) - sum2(ln(y)) ) / ( N sum(ln2(y)) - sum2(ln(y)) )
Excel versions from 2005 (May 2020) to 2103 (March 2021)
For polynomial and logarithmic trendlines, and linear trendlines without a set intercept:
R2(Z,Y) = ( 2 N sum(yz) - N sum(z2) - sum2(y) ) / ( N sum(y2) - sum2(y) )
For power trendlines and exponential trendlines without a set intercept:
R2(Z,Y) = ( 2 N sum(ln(y) ln(z)) - N sum(ln2(z)) - sum2(ln(y)) ) / ( N sum(ln2(y)) - sum2(ln(y)) )
For linear trendlines with a set intercept not equal to zero:
R2(Z,Y) = sum2( ( y - Mean(Y) )( z - Mean(Z) ) ) / ( sum( ( z - Mean(Z) )2 ) sum( ( y - Mean(Y) )2 ) )
For linear trendlines with a set intercept equal to zero:
R2(Z,Y) = sum(z2) / sum(y2)
For exponential trendlines with a set intercept not equal to one:
R2(Z,Y) = sum2( ( ln(y) - Mean(ln(y)) )( ln(z) - Mean(ln(z)) ) ) / ( sum( ( ln(z) - Mean(ln(z)) )2 ) sum( ( ln(y) - Mean(ln(y)) )2 ) )
For exponential trendlines with a set intercept equal to one:
R2(Z,Y) = sum( ln2(z) ) / sum( ln2(y) )
Excel versions 2104 (April 2021) or later
For linear trendlines with a set intercept equal to zero:
R2(Z,Y) = sum(z2) / sum(y2)
For linear trendlines without a set intercept, linear trendlines with a set intercept not equal to zero, polynomial, logarithmic, exponential, and power trendlines:
R2(Z,Y) = sum2( ( y - Mean(Y) )( z - Mean(Z) ) ) / ( sum( ( z - Mean(Z) )2 ) sum( ( y - Mean(Y) )2 ) )
Note: Polynomial trendlines with set intercepts have more numerical precision errors than other trendline types.
