LINEST() function calculation

%3CLINGO-SUB%20id%3D%22lingo-sub-3196244%22%20slang%3D%22en-US%22%3ELINEST()%20function%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3196244%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3CBR%20%2F%3EI'm%20working%20on%20a%20requirement%20where%20I%20try%20to%20replicate%20a%20scatter%20graph%20with%20a%20trendline%20from%20Excel%20to%20Power%20Bi.%20Can%20someone%20explain%20the%20%3CSTRONG%3Eunderlying%20calculation%20of%20the%20LINEST()%20function%3C%2FSTRONG%3E%2C%20what%20kind%20of%20calculation%20it%20does%20with%20the%20data%20when%20we%20give%20known%20Y's%20and%20known%20X's%20when%20set%20const%20parameter%20as%20false.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3E%3CSTRONG%3ELINEST(%26nbsp%3B%3CSPAN%20class%3D%22%22%3Eknown_y's%3C%2FSPAN%3E%2C%26nbsp%3B%3CSPAN%20class%3D%22%22%3E%5Bknown_x's%5D%3C%2FSPAN%3E%2C%26nbsp%3BFALSE%26nbsp%3B)%3C%2FSTRONG%3E%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3196244%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3200582%22%20slang%3D%22en-US%22%3ERe%3A%20LINEST()%20function%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3200582%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1314084%22%20target%3D%22_blank%22%3E%40Vickyswe%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELINEST(Y%2C%20X%2C%20FALSE)%20results%20in%20y%20%3D%20m*x%2C%20where%20apparently%2C%20m%20is%20effectively%20%5B1%5D%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23DF0000%22%3ESUMPRODUCT(Y%2C%20X)%20%2F%20SUMSQ(X)%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20you%20might%20reconsider%20using%20const%3DFALSE%2C%20in%20the%20first%20place.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20is%20some%20disagreement%20among%20academics.%26nbsp%3B%20Consider%20the%20following%20simple%20example.%26nbsp%3B%20X%3D%7B1%2C2%2C3%7D%20and%20Y%3D%7B11%2C12%2C13%7D.%26nbsp%3B%20Obviously%2C%20y%20%3D%20x%2B10.%26nbsp%3B%20And%20the%20trendline%20fits%20perfectly%2C%20to%20wit%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22JoeUser_2-1645556310167.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F350454i4E7C3B20276C60C1%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22JoeUser_2-1645556310167.png%22%20alt%3D%22JoeUser_2-1645556310167.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20with%20const%3DFALSE%20(intercept%3D0)%2C%20the%20trendline%20does%20not%20even%20come%20close%2C%20IMHO%20(notwithstanding%20R%5E2)%2C%20to%20wit%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22JoeUser_1-1645556250631.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F350453i7E8F0A3602F10C87%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22JoeUser_1-1645556250631.png%22%20alt%3D%22JoeUser_1-1645556250631.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EInstead%20of%20const%3DFALSE%2C%20I%20have%20no%20problem%20with%20%22discontinuities%22.%26nbsp%3B%20For%20example%2C%3C%2FP%3E%3CP%3Ey%20%3D%20x%2B10%20for%20x%26gt%3B0%2C%20but%20y%3D0%20for%20x%26lt%3B%3D0.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlternatively%2C%20it%20is%20not%20uncommon%20to%20have%20different%20linear%20regressions%20for%20different%20ranges%20of%20x.%26nbsp%3B%20For%20example%2C%20y%20%3D%20x%2B10%20for%20x%26gt%3B%3D1%2C%20and%20y%20%3D%2011x%20for%20x%26lt%3B1.%26nbsp%3B%20(And%20y%3D0%20for%20x%26lt%3B0.)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-----%3C%2FP%3E%3CP%3E%5B1%5D%20Reference%20(TMI)%3A%26nbsp%3B%20%3CA%20href%3D%22http%3A%2F%2Fweb.ist.utl.pt%2F~ist11038%2Fcompute%2Ferrtheory%2F%2Cregression%2Fregrthroughorigin.pdf%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fweb.ist.utl.pt%2F~ist11038%2Fcompute%2Ferrtheory%2F%2Cregression%2Fregrthroughorigin.pdf%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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

@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:

 

JoeUser_2-1645556310167.png

 

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

 

JoeUser_1-1645556250631.png

 

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