Chapter 3: Formulas and Functions – Completing Modeling Tasks with a Single Formula

One of the first things that makes Excel more than a glorified electronic calculator is its use of functions and formulas. This feature allows Excel to combine a number of mathematical tasks—some of which can be quite complex—into a single function.

In this chapter, you will learn how to use formulas and will understand a selection of the most widely used functions.

The following topics will be covered in this chapter:

• Understanding functions and formulas
• Working with lookup functions
• Utility functions
• Pivot tables and charts
• Pitfalls to avoid
• New functions in Excel 365

Understanding functions and formulas

In order to enter either a formula or a function, you must first type =. A formula is a statement that includes one or more operands (+, -, /, *, and ^), such as =34+7 or =A3-G5 (this formula subtracts the contents of cell G5 from the contents of cell A3). A function can also be included as part of a formula, such as =SUM(B3:B7)*A3. This formula will add the contents of cells B3 to B7 and multiply the result by the contents of cell A3.

A function is a command that contains a series of instructions for Excel to carry out. A function contains one or more arguments, inviting the user to specify the input cell or range of cells on which the instructions are to be carried out, for example, MATCH(A5, F4:F23,false).

A function can include a formula as part of an argument, such as =IF(A4*B4>C4,D4,E4).

However, the distinction between them is often ignored and the term formula is used to indicate either a formula or function.

To enter a formula...

Working with lookup functions

Lookup functions are some of the most widely used functions in Excel. Generally, the intention is to fetch a value from one dataset (the source) to another dataset (the target).

Let's first understand what a proper Excel dataset is.

The first row of a dataset is the header row that contains the names of all the fields. As you can see in the illustration in Figure 3.1, the header row of the sales report includes the following fields: Date, Product, Product Code, Salesperson, and so on. Each column in the dataset represents a field, and each row represents a record. Finally, no entire row or entire column in the dataset must be empty, and there must be at least one empty row below the dataset, at least one empty column to the right, one empty row above (unless the dataset begins from row 1), and one empty column to the left (unless the dataset begins from column A) of the dataset.

For example, say you have two datasets: a sales report that...

Utility type functions

Utility type functions can be used on their own. However, they come into their own when embedded in other, more complex functions. In such cases, they expand the scope and functionality of the enclosing function by providing access to more conditions or variables.

Some examples of utility type functions are IF, AND, OR, MAX, MIN, and MATCH. We will now look at a few of them here.

The IF function

This is one of the most widely used functions in Excel. It can be used on its own or as part of another formula. The IF function checks whether a condition is met, then returns one value if it is and another value if it isn't. The syntax contains three arguments:

• logical_test: The logical test is a statement that returns a value of true if the condition is met, or false if the condition is not met.
• value_if_true: This argument allows you to specify which value you wish to be returned if the condition is met and the result...

Pivot tables and charts

Pivot tables are one of the most powerful tools in Excel. A pivot table can summarize little or large amounts of data into a compact form that reveals trends and relationships that were not apparent from looking at the original data.

The pivot table allows you to introduce conditions based on the original data so that you can view the summarized data from different perspectives. It does all of this without you having to type any formulas. Most users are under the impression that pivot table reports are complex and difficult to prepare, but, in reality, the complexity is kept behind the scenes and taken care of by Excel. All you have to do is follow a few simple guidelines and you will be able to produce complex pivot tables with ease.

The first step is to ensure that your data is in the proper Excel table format, bearing in mind that you may have to work with data prepared by someone else.

Excel identification and navigation shortcuts depend on your...

Pitfalls to avoid

In constructing your formulas, it is easy to get carried away and, very soon, the formula becomes very complex and unfriendly. While it is desirable to keep formulas compact, they should be simple and easy for a third party to follow. If necessary, break up the formula into two or more parts so that it becomes easier to follow while retaining the original effect.

Alternatively, you can use Alt + Enter to force part of the formula to the next line. This will not affect the result of the formula, but it will make the formula easier to understand. Consider the following example:

=INDEX(C5:G10,MATCH(J20,C5:C10,0),MATCH(K19,C5:G5,0))

This complex formula can be broken down into three parts with the use of Alt + Enter, as follows:

=INDEX(C5:G10,
MATCH(J20,C5:C10,0),
MATCH(K19,C5:G5,0))

The formula remains within the same cell but is displayed on 3 lines. As we can see, this makes it easier to decipher.

Protect sheets

If you are going to share your model...

New functions in Excel 365

With Excel 365, Microsoft has brought array formulas much more into mainstream use by introducing a number of revolutionary functions.

Throughout the book, we will explain these new functions, starting in this chapter with XLOOKUP.

XLOOKUP

This powerful new formula combines the features of VLOOKUP, HLOOKUP, INDEX, and MATCH, and does so in a much-simplified way while introducing a lot more options.

XLOOKUP has six arguments, three of which are compulsory with the other three optional. This does not make it more difficult or complex. Rather, as you will see, its application is quite simple.

The following screenshot shows the six XLOOKUP arguments:

Figure 3.38 – Example showing the full XLOOKUP syntax

Using the same example as in VLOOKUP as shown previously, the first argument, lookup_value, is the same: BN001 in cell D5.

We begin to see the differences with the second argument, lookup_array.

The following...

Summary

In this chapter, we learned about the power of formulas and functions and how we can use them to speed up our modeling and make it more interesting. We worked through examples of some of the more common functions, such as the VLOOKUP, MATCH, and CHOOSE functions. We were also introduced to some of the new functions introduced in Excel 365: XLOOKUP, FILTER, UNIQUE, and SORT. We will see another new function, SEQUENCE, in a practical example in Chapter 7, Asset and Debt Schedules.

In the next chapter, we will look at one of the features that forms the backbone of Excel, the referencing framework. Understanding this framework and knowing how to apply its principles will help to speed up your work and improve your productivity.