Principles of Financial Modelling

CHAPTER 21Statistical Functions

INTRODUCTION

This chapter provides examples of many of the functions in Excel's Statistical category. These are often required to conduct data analysis, investigate relationships between variables, and to estimate or calibrate model inputs or results. Most of the calculations concern:

• The position, ranking, spread and shape of a data set.
• Probability distributions (X-to-P and P-to-X calculations).
• Co-relationships and regression analysis.
• Forecasting and other statistical calculations.

Since Excel 2010, the category has undergone significant changes, which were introduced for several reasons:

• To provide clearer definitions, for example as to whether the calculations are based on samples of data or on full population statistics.
• To allow additional function possibilities and user-options, such as whether probability distributions are expressed in their cumulative or density form.
• To create new types of calculations that previously may not have existed, such as those within the FORECAST-related suite of functions.

In order to create backward compatibility with earlier versions of Excel, many of the changes have been introduced by naming the new functions with a “.”, such as MODE.SNGL, or RANK.EQ or STDEV.S.

Note that Excel's function categories are in some cases slightly arbitrary constructs, with the result that some functions are not necessarily in the category that one might expect. In particular, the distinction between Statistical and ...