Home

Analysis ToolPack Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-753319%22%20slang%3D%22en-US%22%3EAnalysis%20ToolPack%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-753319%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20using%20the%20Analysis%20ToolPack%20on%20Excel%20in%20order%20to%20estimate%20some%20regressions%2C%20etc%20but%20I%20cannot%20apply%20it%20more%20than%20once%20otherwise%20it%20gives%20me%20an%20error.%20Do%20you%20have%20any%20suggestions%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-753319%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%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-753653%22%20slang%3D%22en-US%22%3ERe%3A%20Analysis%20ToolPack%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-753653%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F368711%22%20target%3D%22_blank%22%3E%40chiaretta%3C%2FA%3E%2C%3C%2FP%3E%0A%3CP%3EFWIW%2C%20I%20never%20use%20the%20Analysis%20ToolPak%20to%20do%20regression%20analysis.%20Instead%2C%20I%20use%20LINEST%20function%20array-entered%20into%20a%20range%20of%20cells.%20The%20number%20of%20rows%20is%20fixed%20at%205%2C%20but%20the%20number%20of%20columns%20should%20equal%20the%20number%20of%20independent%20variables%20plus%201.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20suppose%20I%20have%20x%20data%20in%20A2%3AA20%20and%20y%20data%20in%20B2%3AB20%20and%20want%20to%20do%20a%20regression%20analysis%20using%20a%20cubic%20polynomial.%20I%20would%20select%20a%205%20row%20by%204%20column%20range%20of%20cells%2C%20then%20array-enter%20this%20formula%3A%3C%2FP%3E%0A%3CPRE%3E%3DLINEST(%24B%242%3A%24B%2420%2C%24A%242%3A%24A%2420%5E%7B1%2C2%2C3%7D%2CTRUE%2CTRUE)%3C%2FPRE%3E%0A%3CP%3EArray-entering%20means%20holding%20the%20Control%20and%20Shift%20keys%20down%20while%20hitting%20Enter.%20Excel%20will%20add%20curly%20braces%20surrounding%20your%20formula%20if%20you%20do%20it%20right.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20reason%20for%20selecting%20a%205%20row%20by%204%20column%20range%20of%20cells%20is%20that%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Flinest-function-84d7d0d9-6e50-4101-977a-fa7abf772b6d%3FNS%3DEXCEL%26amp%3BVersion%3D90%26amp%3BSysLcid%3D1033%26amp%3BUiLcid%3D1033%26amp%3BAppVer%3DZXL900%26amp%3BHelpId%3Dxlmain11.chm60097%26amp%3Bui%3Den-US%26amp%3Brs%3Den-US%26amp%3Bad%3DUS%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3ELINEST%20returns%20an%20array%20of%20data%3C%2FA%3E.%20The%20first%20row%20contains%20the%20coefficients%20in%20reverse%20order%20(constant%20on%20the%20right%2C%20cubic%20term%20on%20the%20left).%20The%20third%20row%20contains%20the%20R-squared.%20The%20fourth%20row%20contains%20the%20F%20statistic%20and%20degrees%20of%20freedom.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20have%20multiple%20independent%20variables%20in%20cells%20C2%3AE20%2C%20your%20formula%20would%20be%3A%3C%2FP%3E%0A%3CPRE%3E%3DLINEST(B2%3AB20%2CC2%3AE20%2CTRUE%2CTRUE)%3C%2FPRE%3E%0A%3CP%3EThis%20formula%20would%20still%20be%20array-entered%20into%20a%205%20row%20by%204%20column%20range%20of%20cells.%3C%2FP%3E%3C%2FLINGO-BODY%3E
chiaretta
Occasional Visitor

Hi, 

 

I am using the Analysis ToolPack on Excel in order to estimate some regressions, etc but I cannot apply it more than once otherwise it gives me an error. Do you have any suggestions?

 

Thank you in advance

1 Reply

@chiaretta,

FWIW, I never use the Analysis ToolPak to do regression analysis. Instead, I use LINEST function array-entered into a range of cells. The number of rows is fixed at 5, but the number of columns should equal the number of independent variables plus 1.

 

Let's suppose I have x data in A2:A20 and y data in B2:B20 and want to do a regression analysis using a cubic polynomial. I would select a 5 row by 4 column range of cells, then array-enter this formula:

=LINEST($B$2:$B$20,$A$2:$A$20^{1,2,3},TRUE,TRUE)

Array-entering means holding the Control and Shift keys down while hitting Enter. Excel will add curly braces surrounding your formula if you do it right.

 

The reason for selecting a 5 row by 4 column range of cells is that LINEST returns an array of data. The first row contains the coefficients in reverse order (constant on the right, cubic term on the left). The third row contains the R-squared. The fourth row contains the F statistic and degrees of freedom.

 

If you have multiple independent variables in cells C2:E20, your formula would be:

=LINEST(B2:B20,C2:E20,TRUE,TRUE)

This formula would still be array-entered into a 5 row by 4 column range of cells.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies