O'Reilly logo

Using Excel for Business Analysis: A Guide to Financial Modelling Fundamentals, + Website by Danielle Stein Fairhurst

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

CHAPTER 6

Functions for Financial Modelling

In this chapter, we will introduce the most useful functions commonly used in financial modelling. Whilst there are hundreds of functions that can be used in a model, I have attempted to only list the ones you are most likely to come across, and that you will find most useful when building models. Please don’t stop here, though! There are many more wonderful Excel functions that you will find useful in your financial modelling and business analysis.

AGGREGATION FUNCTIONS

COUNTIF, SUMIF, COUNTIFS, and SUMIFS are very handy functions to know for modelling. They add or count ranges of data.

COUNTIF

COUNTIF is used to count the cells that match specified criteria.

For example, let’s say you have sold $497 worth of different products on a particular day, as shown in Figure 6.1.

FIGURE 6.1 Sales List

image

You’d like to know how many (in terms of number of products) you’ve sold of each product: books, CDs, and DVDs.

1. In range A15:A17, type the words Book, CD, and DVD.
2. In cell B15, use the Formula Wizard and find the COUNTIF function or, if you prefer, simply begin typing =COUNTIF(.
3. If you are using the Formula Wizard, it should look like Figure 6.2.

FIGURE 6.2 COUNTIF Function Wizard Dialog Box

image
4. Press OK. The answer is 4. Your formula ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required