LINEST Mystery output

%3CLINGO-SUB%20id%3D%22lingo-sub-2074922%22%20slang%3D%22en-US%22%3ELINEST%20Mystery%20output%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2074922%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20small%2C%20simple%20table.%20I'm%20trying%20to%20do%20a%20simple%20multiple%20linear%20regression%20analysis.%20I%20get%20a%20single%20digit%20as%20a%20result.%20Here's%20the%20table%20and%20output.%20Here's%20the%20formula%20I%20used%3A%20%3DLINEST(D1%3AD10%2CA1%3AC10%2CTRUE%2CTRUE)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20fear%20the%20answer%20will%20be%20%22You're%20an%20idiot%22%2C%20but%20that%20notwithstanding%2C%20what%20am%20I%20missing%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E19.80%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2017.40%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%205.00%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20-48.00%3C%2FP%3E%3CP%3E17.50%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2015.60%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%205.10%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20-40.00%3C%2FP%3E%3CP%3E31.70%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2013.30%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201.60%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20-39.00%3C%2FP%3E%3CP%3E19.90%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2014.70%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201.70%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20-39.00%3C%2FP%3E%3CP%3E20.30%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2014.30%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%207.20%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20-36.00%3C%2FP%3E%3CP%3E20.60%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201.60%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201.70%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20-30.00%3C%2FP%3E%3CP%3E18.50%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2017.20%26nbsp%3B%26nbsp%3B%26nbsp%3B%2010.60%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20-24.00%3C%2FP%3E%3CP%3E20.90%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2014.00%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%207.80%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20-22.00%3C%2FP%3E%3CP%3E18.20%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2012.70%26nbsp%3B%26nbsp%3B%26nbsp%3B%2012.60%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20-21.00%3C%2FP%3E%3CP%3E19.30%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2014.30%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%200.10%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20-18.00%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E0.635946%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2074922%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2076355%22%20slang%3D%22en-US%22%3ERe%3A%20LINEST%20Mystery%20output%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2076355%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F934861%22%20target%3D%22_blank%22%3E%40hAltonJones%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt's%20not%20clear%20what%20you'd%20like%20to%20receive%20-%20where%20is%20x%2C%20where%20y%2C%20and%20why%20do%20you%20need%20regression%20stats%20array%20with%20last%20parameter.%20Please%20check%20if%20you%20are%20in%20sync%20with%20description%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Flinest-function-84d7d0d9-6e50-4101-977a-fa7abf772b6d%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3ELINEST%20function%20-%20Office%20Support%20(microsoft.com)%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I have a small, simple table. I'm trying to do a simple multiple linear regression analysis. I get a single digit as a result. Here's the table and output. Here's the formula I used: =LINEST(D1:D10,A1:C10,TRUE,TRUE)

 

I fear the answer will be "You're an idiot", but that notwithstanding, what am I missing?

 

Thanks.

 

19.80          17.40     5.00      -48.00

17.50          15.60     5.10      -40.00

31.70          13.30     1.60      -39.00

19.90          14.70     1.70      -39.00

20.30          14.30     7.20      -36.00

20.60            1.60     1.70      -30.00

18.50          17.20    10.60     -24.00

20.90          14.00      7.80     -22.00

18.20          12.70    12.60     -21.00

19.30          14.30     0.10      -18.00

 

0.635946

 

3 Replies

@hAltonJones 

It's not clear what you'd like to receive - where is x, where y, and why do you need regression stats array with last parameter. Please check if you are in sync with description LINEST function - Office Support (microsoft.com)

@Sergei BaklanMy understanding (from the Help file) is that the first parameter is the "Y" column, i.e., the dependent variable, hence, LINEST(D1:D10,A1:C10,TRUE,TRUE). "X" variable, i.e., independent variables per the Help file are the second parameter, i.e., LINEST(D1:D10,A1:C10,TRUE,TRUE). In that I haven't received the results promised in the Help file, I don't know if I need the regression stats array, but I'm guessing it contains valuable information about the strength of the correlations, etc.

@hAltonJones 

I see, thank you. Perhaps you are on Excel without dynamic arrays, in this case you shall use Ctrl+Shift+Enter, step by step tutorial for such is here Excel LINEST function with formula examples (ablebits.com) 

On modern Excel it looks like

image.png