Forum Discussion

Vickyswe's avatar
Vickyswe
Copper Contributor
Feb 22, 2022

LINEST() function calculation

Hi, I'm working on a requirement where I try to replicate a scatter graph with a trendline from Excel to Power Bi. Can someone explain the underlying calculation of the LINEST() function, what kind ...
BI & Data Analysis
excel
Formulas and Functions
power bi
JoeUser2004's avatar
JoeUser2004
Bronze Contributor
Feb 22, 2022

Vickyswe 

 

LINEST(Y, X, FALSE) results in y = m*x, where apparently, m is effectively [1]:

 

SUMPRODUCT(Y, X) / SUMSQ(X)

 

But you might reconsider using const=FALSE, in the first place.

 

There is some disagreement among academics.  Consider the following simple example.  X={1,2,3} and Y={11,12,13}.  Obviously, y = x+10.  And the trendline fits perfectly, to wit:

 

 

But with const=FALSE (intercept=0), the trendline does not even come close, IMHO (notwithstanding R^2), to wit:

 

 

Instead of const=FALSE, I have no problem with "discontinuities".  For example,

y = x+10 for x>0, but y=0 for x<=0.

 

Alternatively, it is not uncommon to have different linear regressions for different ranges of x.  For example, y = x+10 for x>=1, and y = 11x for x<1.  (And y=0 for x<0.)

 

 

-----

[1] Reference (TMI):  http://web.ist.utl.pt/~ist11038/compute/errtheory/,regression/regrthroughorigin.pdf

 

Resources