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
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 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
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies