In the courses I run on Excel I am often required to talk about things like correlation analysis, regression analysis, frequency distributions and so on. That’s fine and commonly I kill two birds with one stone and introduce these topics at the same time as introducing the Data Analysis ToolPak and its installation.
Whilst the Data Analysis ToolPak is good for many things it is limited in that it provides static solutions. That is, once you have prepared the correlation matrix it cannot change until you rework it if, for example, you make changes or corrections to your data set. The same with regression analysis, frequency distributions and so on.
In my book, Excel 2007 with Excel Master, you will see that I have provided both the Data Analysis ToolPak version of the calculations as well as the in built equivalent Excel 2007 functions: =CORRELATION, =LINEST, =FREQUENCY … the in built functions are dynamic and that means if you change the data, make corrections or want to over type your work sheet with a new batch of data, your results will change instantly and automatically to reflect the changes you have made.
Duncan Williamson
Excel Master
Excel with Excel Master 