Advanced Modelling in Finance Using Excel and VBA

Chapter 2. Advanced Excel Functions and Procedures

The purpose of this chapter is to review certain Excel functions and procedures used in the text. These include mathematical, statistical and lookup functions from Excel's extensive range of functions, as well as much-used procedures such as setting up Data Tables and displaying results in XY charts. Also included are methods of summarising data sets, conducting regression analyses, and accessing Excel's Goal Seek and Solver. The objective is to clarify and ensure that this material causes the reader no difficulty. The advanced Excel user may wish to skim the content or use the chapter for further reference as and when required. To make the various topics more entertaining and more interactive, a workbook AMFEXCEL.xls includes the examples discussed in the text and allows the reader to check his or her proficiency.

ACCESSING FUNCTIONS IN EXCEL

Excel provides many worksheet functions, which are essentially calculation routines that have been coded up. They are useful for simplifying calculations performed in the spreadsheet, and also for combining into VBA macros and user-defined functions (topics covered in Chapters 3 and 4).

The Paste Function button (labelled fx) on the standard toolbar gives access to them. (It was previously known as the function wizard.) As Figure 2.1 shows, functions are grouped into different categories: mathematical, statistical, logical, lookup and reference, etc.

Figure 2-1. Paste Function dialog box showing ...