Financial Analysis and Modeling Using Excel and VBA

Chapter 11. Other Useful Excel Functions

In this chapter, I selectively cover Excel's Mathematical, Date and Time, and Text functions that you are likely to use. Excel has many more functions in these categories, which you can look up as necessary.

For a general discussion on Excel's functions and how to learn and use them, see the chapter on advanced Excel features.

Mathematical Functions

In addition to the functions I discuss here, this category includes a complete set of trigoNametric functions and several functions for doing matrix calculations and calculations with arrays. I do not discuss them here because you are not likely to use them much.

TRUNCATING AND ROUNDING NUMBERS

Excel provides several functions for truncating and rounding numbers. The differences among some of them are subtle, and the way they work on negative numbers may be different from what you expect. Be careful about choosing the right function for your application; if in doubt, experiment to make sure you understand how a function will handle negative arguments.

ROUND

Rounds a number to the specified number of digits (the second argument). This function follows the standard mathematical definition of rounding.

ROUND(1.36,l) equals 1.4.

ROUND(−1.36,1) equals −1.4.

ROUNDUP

Rounds a number away from zero to the specified number of digits.

ROUNDUP( 1.34,1) equals 1.4.

ROUNDUP(-1.34,1) equals −1.4.

ROUMDDOWN

Rounds a number toward zero to the specified number of digits.

ROUNDDOWN(l.36,1) equals 1.3.

ROUNDDOWN(-1.36,1) ...