Forum Discussion
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 of calculation it does with the data when we give known Y's and known X's when set const parameter as false.
LINEST( known_y's, [known_x's], FALSE )
The resultant of the above equation gives the adjusted slope value setting the intercept as 0. It would be useful if I get to know, how the slope is corrected.
1 Reply
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
