Using Functions in Excel
using formulas and functions. So, actually, you’re not totally off the hook when it comes to knowing math — you have to understand math in order to know which formula or function to use.
In this chapter, I start by telling you the difference between a formula and a func- tion. Then I explain how to find the function you need, when you’re not quite sure what it’s called. Finally, as a financial modeler, you’ll be expected to have a firm grasp on the most commonly used functions in Excel, at the very least; this chap- ter covers the functions that are absolutely critical for you to know.
Identifying the Diff between
a Formula and a Function
In Excel, functions and formulas both help you calculate an answer. You may hear the two words used interchangeably, but they’re not technically the same. So, what’s the difference? A formula is an expression that uses cell references or
hard-coded numbers to calculate the value of a cell. For example, =A1+A2 and
=923*12 are formulas. Sometimes a simple formula is all you need to get the right answer, but you can do so much more using functions. A function is a predefined formula already available in Excel. Functions streamline the process of creating a calculation. To date, Excel has more than 400 functions. Every time Microsoft releases a new version of Excel, more functions are added.
Functions can do complicated calculations that would be time-consuming to build manually. For example, if you wanted to add up a range of cells without using a function, you’d need to write something like =A1+A2+A3+A4+A5 instead of
=SUM(A1:A5). Now, for five cells, writing it manually isn’t such a big deal. But what if you’re adding a range of hundreds of cells? Or thousands? Functions make calculations a lot easier.
There’s a lot more to being a good financial modeler than simply knowing lots and lots of Excel functions. But the more functions you know, the more likely you are to choose the one that’s most appropriate for the job at hand. As with many things in life, there are usually several ways to achieve the same result, but the best option is the one that’s the clearest and easiest for others to understand, as well as the simplest to audit (see Chapter 5 for more on formula auditing).
Finding the Function You Need
If you’re trying to perform a calculation in Excel and you aren’t sure what the
function you want is called, don’t worry! Just follow these steps:
-
Click the Formulas tab.
-
Click the Insert Function button.
The Insert Function dialog box, shown in Figure 7-1, appears.You can also access the Insert Function dialog box by clicking the fx button to the left of the Formula Bar or by using the shortcut Shift+F3.
-
In the Search for a Function text box, type a brief description of what you want to do, and then click Go.A list of functions appears in the Select a Function box.
-
If you’re still not fi the function you’re looking for, select a category
of functions from the Select a Category drop-down list to narrow the list.
FIGURE 7-1:
The Insert Function dialog box.
-
When you’ve found the function you think might be right, select it from the Select a Function box and click OK.The Insert Function dialog box appears. Click the Help on This Function link to get even more help on the function you’ve selected.
