Financial Analysis and Modeling Using Excel and VBA

Chapter 9. Doing Statistical Analysis

In the Statistical group of its function library Excel provides a large number of statistical functions. In addition Excel includes an add-in called Analysis ToolPak, which incorporates a number of tools for doing more comprehensive statistical analysis. Together the statistical functions and the tools in the Analysis ToolPak cover most types of statistical analysis that you would need to do in finance and include in your models. So you should familiarize yourself with what functions and tools are available and then learn them in more detail when the need arises. (Note that in the earlier versions of Excel many functions were included in the Analysis ToolPak, which have now been integrated into the function library in Excel 2007. If you are using an earlier version of Excel and cannot find a function I mention in the function library, you will have to first install the Analysis ToolPak, as explained later.)

In both the function library and Excel's Help, functions are listed alphabetically. This is not very helpful. Here I will group together statistical functions and tools from the Analysis ToolPak by usage to make it easier for you to find the function or tool you need for any situation.

Many people look at functions as "black boxes." You input the arguments, and the functions provide the answers. In a way this view is true, but it is also dangerous. You should be especially careful about using the statistical functions and tools. They make ...