Feb 21 2022 06:23 PM - edited Feb 22 2022 12:11 AM
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.
Feb 22 2022 11:11 AM - edited Feb 22 2022 11:13 AM
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