My Office 2016 (2016)

A. Excel Worksheet Function Reference

As you learned in Chapter 6, “Entering Excel Data,” to get to the real meat of a spreadsheet model, you need to expand your formula repertoire to include Excel’s worksheet functions. Several hundred of these functions exist, and they’re an essential part of making your worksheet work easier and more powerfully. Excel has various function categories, including the following:

• Financial

• Logical

• Text

• Date and time

• Lookup and reference

• Math and trigonometry

• Statistical

• Engineering

• Information

• Database

• Cube

• Compatibility

• Web

(Note that this list, as well as the sections that follow, are presented in the order that the function category buttons appear in Excel’s Insert Function dialog box.)

Functions are formulas that Excel has predefined. They’re designed to take you beyond the basic arithmetic and text formulas you’ve seen so far. They do this in three ways:

• Functions make simple but cumbersome formulas easier to use. For example, suppose that you want to add a list of 100 numbers in a column starting at cell A1 and finishing at cell A100. Even if you wanted to, you couldn’t enter 100 separate additions in a cell because you would run out of room. (Recall that cells are limited to 255 characters.) Luckily, there’s an alternative: the SUM() function. With this function, you can simply enter =SUM(A1:A100).

• Functions enable you to include complex mathematical expressions in your worksheets that otherwise would be difficult or impossible to construct using simple arithmetic operators. For example, determining a mortgage payment given the principal, interest, and term is a complicated matter, at best, but you can do it with Excel’s PMT() function just by entering a few parameters.

• Functions enable you to include data in your applications that you couldn’t access otherwise. For example, the INFO() function can tell you how much memory is available on your system, what operating system you’re using, what version number it is, and more. Similarly, the powerful IF() function enables you to test the contents of a cell—for example, to see whether it contains a particular value or an error—and then perform an action accordingly, depending on the result.

As you can see, functions are a powerful addition to your worksheet-building arsenal. With proper use of these tools, there is no practical limit to the kinds of models you can create.

Every function has the same basic form:

FUNCTION(argument1, argument2, ...)

The FUNCTION part is just the name of the function, which always appears in uppercase letters (such as SUM or PMT). Note, however, that you don’t need to type in the function name using uppercase letters. Whatever case you use, Excel automatically converts the name to all uppercase. In fact, it’s good practice to enter function names using only lowercase letters. That way, if Excel doesn’t convert the function name to uppercase, you know that it doesn’t recognize the name, which means you probably misspelled it.

The items that appear within the parentheses and separated by commas are the function arguments. The arguments are the function’s inputs—the data it uses to perform its calculations. With respect to arguments, functions come in two flavors:

• No arguments—Many functions don’t require any arguments. For example, the NOW() function returns the current date and time, and doesn’t require arguments.

• One or more arguments—Most functions accept at least 1 argument, and some accept as many as 9 or 10 arguments. These arguments fall into two categories: required and optional. The required arguments must appear between the parentheses, or the formula will generate an error. You use the optional arguments only if your formula needs them.

Let’s look at an example. The FV() function determines the future value of a regular investment based on three required arguments and two optional ones:

This is called the function syntax. Three conventions are at work here and in Excel:

• Italic type indicates a placeholder—when you use the function, you replace the placeholder with an actual value.

• Arguments shown in bold type are required.

• Arguments shown in regular type are optional.

For each required argument placeholder (and whatever optional argument you want to include), you substitute an appropriate value. For example, in the FV() function, you substitute rate with a decimal value between 0 and 1, nper with an integer, and pmt with a dollar amount. Arguments can take any of the following forms:

• Literal alphanumeric values

• Expressions

• Cell or range references

• Range names

• Arrays

• The result of another function

The function operates by processing the inputs and then returning a result. For example, the FV() function returns the total value of the investment at the end of the term.

Financial Functions

Excel is loaded with financial features that give you powerful tools for building models that manage both business and personal finances. You can use these functions to calculate such things as the monthly payment on a loan, the future value of an annuity, the internal rate of return of an investment, or the yearly depreciation of an asset.

Most of the formulas you’ll work with involve three factors—the present value (the amount something is worth now), the future value (the amount something is worth in the future), and the interest rate (or the discount rate)—plus two related factors: the periods, the number of payments or deposits over the term of the loan or investment, and the payment, the amount of money paid out or invested in each period.

When building your financial formulas, you need to ask yourself the following questions:

• Who or what is the subject of the formula? On a mortgage analysis, for example, are you performing the analysis on behalf of yourself or the bank?

• Which way is the money flowing with respect to the subject? For the present value, future value, and payment, enter money that the subject receives as a positive quantity, and enter money that the subject pays out as a negative quantity. For example, if you’re the subject of a mortgage analysis, the loan principal (the present value) is a positive number because it’s money that you receive from the bank; the payment and the remaining principal (the future value) are negative because they’re amounts that you pay to the bank.

• What is the time unit? The underlying unit of both the interest rate and the period must be the same. For example, if you’re working with the annual interest rate, you must express the period in years. Similarly, if you’re working with monthly periods, you must use a monthly interest rate.

• When are the payments made? Excel differentiates between payments made at the end of each period and those made at the beginning.

Table A.1 gives you a list of all Excel’s worksheet functions in the Financial category.

Table A.1 Excel’s Financial Functions

Date and Time Functions

The date and time functions enable you to convert dates and times to serial numbers and perform operations on those numbers. This capability is useful for such things as accounts receivable aging, project scheduling, time-management applications, and much more.

Table A.2 gives you a list of all Excel’s worksheet functions in the Date and Time category.

Table A.2 Excel’s Date and Time Functions

Math and Trigonometry Functions

Excel’s mathematical underpinnings are revealed when you consider the long list of math-related functions that come with the program. There are functions for basic mathematical operations such as absolute values, lowest and greatest common denominators, square roots, and sums. There are also plenty of high-end operations for things like matrix multiplication, multinomials, and sums of squares. Table A.3 lists the Excel math and trigonometry functions.

Table A.3 Excel’s Math and Trigonometry Functions

Statistical Functions

Excel’s statistical functions calculate all the standard statistical measures such as average, maximum, minimum, and standard deviation. For most of the statistical functions, you supply a list of values (called a sample or population). You can enter individual values or cells, or you can specify a range. Table A.4 lists all Excel’s worksheet functions in the Statistical category.

Table A.4 Excel’s Statistical Functions

Lookup and Reference Functions

Getting the meaning of a word in the dictionary is always a two-step process: First, you look up the word and then you read its definition. This idea of looking something up to retrieve some related information is at the heart of many spreadsheet operations. For example, the value of one argument often depends on the value of another. Here are some examples:

• In a formula that calculates an invoice total, the customer’s discount might depend on the number of units purchased.

• In a formula that charges interest on overdue accounts, the interest percentage might depend on the number of days each invoice is overdue.

• In a formula that calculates employee bonuses as a percentage of salary, the percentage might depend on how much the employee improved upon the given budget.

The usual way to handle these kinds of problems is to look up the appropriate value, and Excel offers a number of functions that enable you to perform lookup operations in your worksheet models. Table A.5 lists Excel’s lookup functions.

Table A.5 Excel’s Lookup and Reference Functions

Database Functions

To get more control over your table analysis, you can use Excel’s database functions. Table A.6 offers a complete list of Excel’s worksheet functions in the Database category.

Table A.6 Excel’s Database Functions

Text Functions

In Excel, text is any collection of alphanumeric characters that isn’t a numeric value, a date or time value, or a formula. Words, names, and labels are all obviously text values, but so are cell values preceded by an apostrophe (‘) or formatted as Text. Text values are also called strings. Text formulas consist only of the concatenation operator (&) used to combine two or more strings into a larger string.

Excel’s text functions enable you to take text formulas to a more useful level by giving you numerous ways to manipulate strings. With these functions, you can convert numbers to strings, change lowercase letters to uppercase (and vice versa), compare two strings, and more. Table A.7 gives you a list of all Excel’s worksheet functions in the Text category.

Table A.7 Excel’s Text Functions

Logical Functions

You can bring some measure of “intelligence” to your worksheets, meaning your formulas can test the values in cells and ranges, and then return results based on those tests. This is all done with Excel’s logical functions, which are designed to create decision-making formulas. For example, you can test cell contents to see whether they’re numbers or labels, or you can test formula results for errors. Table A.8 summarizes Excel’s logical functions.

Table A.8 Excel’s Logical Functions

Information Functions

Excel’s information functions return data concerning cells, worksheets, and formula results. Table A.9 lists all the information functions.

Table A.9 Excel’s Information Functions

Engineering Functions

Excel offers quite a few functions of use to engineers. Table A.10 gives you a list of all Excel’s worksheet functions in the Engineering category.

Table A.10 Excel’s Engineering Functions

Cube Functions

If you work with the multidimensional data structures called cubes, Excel offers a few functions that can make cube analysis easier. Table A.11 gives you a list of all Excel’s worksheet functions in the Cube category.

Table A.11 Excel’s Cube Functions

Compatibility Functions

Excel has many functions that have been replaced by newer versions, and you can still access those old functions if you need to create a model that work in older versions of Excel. Table A.12 gives you a list of all Excel’s worksheet functions in the Compatibility category.

Table A.12 Excel’s Compatibility Functions

Web Functions

Excel has a few functions that enable you to incorporate web-based data into your spreadsheets. Table A.13 lists all Excel’s worksheet functions in the web category.

Table A.13 Excel’s Web Functions