New book: Microsoft Excel 2010 Inside Out
Greetings. Mark Dodge and Craig Stinson’s Microsoft Excel 2010 Inside Out (Microsoft Press, 2011; 1152 pages) is now available!
The book’s chapter-level Table of Contents and a stretch of Chapter 1, “What’s New in Microsoft Excel 2010,” can be found here.
In this post we’d like to share an excerpt from the book. Enjoy!
CHAPTER 14
Everyday Functions
Understanding Mathematical Functions … 537
Understanding Text Functions … 544
Understanding Logical Functions … 550
Understanding Information Functions … 553
Understanding Lookup and Reference Functions … 555
This chapter describes some of the more useful functions Microsoft Excel 2010 has to offer. To keep this book from threatening the structural integrity of your bookshelf, we’ve had to make some hard choices about which functions to highlight. Therefore, this chapter (along with Chapters 15, 16, and 17) by no means represents a comprehensive reference. For complete information about all the built-in functions that Excel 2010 has to offer, you can use a number of on-screen tools, which are covered in “Using the Built-In Function Reference in Excel” on page 527. Appendix C, “Function Reference,” lists every function available in Excel, along with the basic information you need to put each one to use, and includes cross-references to any information available in this and other chapters.
For more information, see Chapter 15, “Formatting and Calculating Date and Time”; Chapter 16, “Functions for Financial Analysis”; and Chapter 17, “Functions for Analyzing Statistics.”
Understanding Mathematical Functions
Most of the work you do in Excel probably involves at least a few mathematical functions. The most popular among these is the SUM function, but Excel is capable of calculating just about anything. In the next sections, we discuss some of the most used (and most useful) mathematical functions in Excel.
Using the SUM Function
The SUM function totals a series of numbers. It takes the form =SUM(number1, number2, . . .). The number arguments are a series of as many as 30 entries that can be numbers, formulas, ranges, or cell references that result in numbers. SUM ignores arguments that refer to text values, logical values, or blank cells.
The Sum Button
Because SUM is such a commonly used function, Excel provides the Sum button on the Home tab on the ribbon, as well as the AutoSum button on the Formulas tab. In addition to SUM, these buttons include a menu of other commonly used functions. If you select a cell and click the Sum button, Excel creates a SUM formula and guesses which cells you want to total. To enter SUM formulas in a range of cells, select the cells before clicking Sum.
Automatic Range Expansion Ever since the first spreadsheet program was created, one of the most common problems has been inserting cells at the bottom or to the right of a range that is already referenced in a formula. For example, suppose you type the formula =SUM(A1:A4) in cell A5 and then select row 5 and insert a new row. The new row is inserted above the selected row, thus pushing the SUM formula down to cell A6. It used to be that any numbers in the inserted cell A5 were not included in the SUM formula. A few versions ago, Excel changed all that. Now you can insert cells at the bottom or to the right of a range referenced by a formula, and Excel adjusts the formulas for you—and this is key—as soon as you type values in the inserted cells. In other words, the SUM formula does not change unless and until you type a value in the inserted cell, now or later. This adjustment also works if, instead of inserting cells, you simply place the formula away from a column of numbers—in fact, it doesn’t matter how many rows are between the formula and the values, as long as they are blank to start. (This does not work with formulas placed to the right of values that refer to columns.) If you type a value in the cell directly below the column of values that are referenced in the formula, the formula adjusts to accommodate it. Automatic range expansion works only immediately to the right or below a referenced range. Inserting cells at the top or to the left of a referenced range still requires you to edit the referencing formulas manually.
The SUMIF, SUMIFS, and COUNTIF Functions
The SUMIF function is similar to SUM, but it first tests each cell using a specified conditional test before adding it to the total. This function takes the arguments (range, criteria, sum_range). The range argument specifies the range you want to test, the criteria argument specifies the conditional test to be performed on each cell in the range, and the sum_range argument specifies the cells to be totaled. For example, if you have a worksheet with a column of month names defined using the range name Months and an adjacent column of numbers named Sales, use the formula =SUMIF(Months, "June", Sales) to return the value in the Sales cell that is adjacent to the label June. Alternatively, you can use a conditional test formula such as =SUMIF(Sales, ">=999", Sales) to return the total of all sales figures that are
more than $999.
The SUMIFS function does similar work to that of the SUMIF function, except you can specify up to 127 different ranges to sum, each with their own criteria. Note that in this function, the sum_range argument is in the first position instead of the third position: (sum_range, criteria_range1, criteria1, criteria_range2, criteria2, . . .). The sum range and
each criteria range must all be the same size and shape. Using a similar example to the one we used for the SUMIF function, suppose we also created defined names for cell ranges Months, Totals, Product1, Product2, and so on. The formula =SUMIFS(Totals, Product3, "<=124", Months, "June") returns the total sales for the month of June when sales of Product3 were less than or equal to $124.
Similarly, COUNTIF counts the cells that match specified criteria and takes the arguments (range, criteria). Using the same example, you can find the number of months in which total sales fell to less than $600 by using a conditional test, as in the formula =COUNTIF(Totals, "<600").
For more information about conditional tests, see “Creating Conditional Tests” on page 522. For more about using range names, see “Naming Cells and Cell Ranges” on page 483.
Using Selected Mathematical Functions
Excel has over 60 built-in math and trigonometry functions; the following sections brush only the surface, covering a few of the more useful or misunderstood functions. You can access them directly by clicking the Math & Trig button on the Formulas tab on the ribbon.
The PRODUCT and SUMPRODUCT Functions
The PRODUCT function multiplies all its arguments and can take as many as 255 arguments that are text or logical values; the function ignores blank cells.
You can use the SUMPRODUCT function to multiply the value in each cell in one range by the corresponding cell in another range of equal size and then add the results. You can include up to 255 arrays as arguments, but each array must have the same dimensions. (Non-numeric entries are treated as zero.) For example, the following formulas are essentially the same:
=SUMPRODUCT(A1:A4, B1:B4)
{=SUM(A1:A4*B1:B4)}
The only difference between them is that you must enter the SUM formula as an array by pressing Ctrl+Shift+Enter.
For more information about arrays, see “Using Arrays” on page 512.
The MOD Function
The MOD function returns the remainder of a division operation (modulus). It takes the arguments (number, divisor). The result of the MOD function is the remainder produced when number is divided by divisor. For example, the function =MOD(9, 4) returns 1, the remainder that results from dividing 9 by 4.
The COMBIN Function
The COMBIN function determines the number of possible combinations, or groups, that can be taken from a pool of items. It takes the arguments (number, number_chosen), where number is the total number of items in the pool and number_chosen is the number of items you want to group in each combination. For example, to determine how many different 11-player football teams you can create from a pool of 17 players, type the formula =COMBIN(17, 11) . The result indicates that you could create 12,376 teams.
Try Your Luck The COMBIN function can help you figure out just how slim a chance you have of drawing the elusive ace-high straight flush in a game of five-card stud. You express the number of 5-card combinations by using the formula =COMBIN(52, 5), resulting in 2,598,960. That’s not too bad when you consider the odds of winning the lottery. For a lotto game in which you select 6 numbers out of a total of 49, the formula =COMBIN(49, 6) reveals that there are 13,983,816 possibilities. You’d better keep your day job either way.
The RAND and RANDBETWEEN Functions
The RAND function generates a random number between 0 and 1. It’s one of the few Excel functions that doesn’t take an argument, but you must still type a pair of parentheses after the function name. The result of a RAND function changes each time you recalculate your worksheet. This is called a volatile function. If you use automatic recalculation, the value of the RAND function changes each time you make a worksheet entry.
The RANDBETWEEN function provides more control than RAND. With RANDBETWEEN, you can specify a range of numbers within which to generate random integer values. The arguments (bottom, top) represent the smallest and largest integers that the function should use. The values for these arguments are inclusive. For example, the formula =RANDBETWEEN(123, 456) can return any integer from 123 up to and including 456.
A MOD Example Here’s a practical use of the MOD function that you can ponder:
1. Select a range of cells such as B5:F16, click Conditional Formatting on the Home tab on the ribbon, and then click New Rule.
2. Select the Use A Formula To Determine Which Cells To Format option in the Select A Rule Type list.
3. In the text box, type the formula =MOD(ROW(), 2)=0.
4. Click the Format button, and select a color on the Fill tab to create a format that applies the selected color to every other row. Note that if you select a single cell in an odd-numbered row before creating this formatting formula, nothing seems to happen, but if you copy or apply the format to other rows, you’ll see the result. Click OK.
We clicked the Conditional Formatting button and clicked Manage Rules to display the dialog box shown in the preceding figure. The MOD formula identifies the current row number using the ROW function, divides it by 2, and if there is a remainder (indicating an odd-numbered row), returns FALSE because the formula also contains the conditional test =0. If MOD returns anything but 0 as a remainder, the condition tests FALSE. Therefore, Excel applies formatting only when the formula returns TRUE (in even-numbered rows). For more information about conditional formatting, see “Formatting Conditionally” on page 309. You can also achieve similar results (with additional functionality) by converting the cell range into a table and using the table formatting features. For more information, see Chapter 22, “Managing Information in Tables.”
Using the Rounding Functions
Excel includes several functions devoted to the seemingly narrow task of rounding numbers by a specified amount.
The ROUND, ROUNDDOWN, and ROUNDUP Functions
The ROUND function rounds a value to a specified number of decimal places. Digits to the right of the decimal point that are less than 5 are rounded down, and digits greater than or equal to 5 are rounded up. It takes the arguments (number, num_digits). If num_digits is a positive number, then number is rounded to the specified number of decimal points; if num_digits is negative, the function rounds to the left of the decimal point; if num_digits is 0, the function rounds to the nearest integer. For example, the formula =ROUND(123.4567, –2) returns 100, and the formula =ROUND(123.4567, 3) returns 123.457. The ROUNDDOWN and ROUNDUP functions take the same form as ROUND. As their names imply, they always round down or up, respectively.
CAUTION! Don’t confuse the rounding functions with rounded number formats, such as the one applied when you click the Accounting Number Format button on the Home tab on the ribbon. When you format the contents of a cell to a specified number of decimal places, you change only the display of the number in the cell; you don’t change the cell’s value. When performing calculations, Excel always uses the underlying value, not the displayed value. Conversely, the rounding functions permanently change the underlying values.
The EVEN and ODD Functions
The EVEN function rounds a number up to the nearest even integer. The ODD function rounds a number up to the nearest odd integer. Negative numbers are correspondingly rounded down. For example, the formula =EVEN(22.4) returns 24, and the formula =ODD(–4) returns –5.
The FLOOR and CEILING Functions
The FLOOR function rounds a number down to its nearest given multiple, and the CEILING function rounds a number up to its nearest given multiple. These functions take the arguments (number, multiple). For example, the formula =FLOOR(23.4, 0.5) returns 23, and the formula =CEILING(5, 1.5) returns 6, the nearest multiple of 1.5. The FLOOR.PRECISE and CEILING.PRECISE functions (both new in Excel 2010) round numbers down or up to the nearest integer or multiple of significance. Both take the arguments (number, significance). For example, the formula =FLOOR.PRECISE(23.4, 4) returns 20, which is the nearest integer below 23.4 that is a multiple of 4. Most of the time, you see no difference in results between the regular and precise versions of these functions, unless your arguments are negative numbers. The precise versions always round up, regardless of the number’s sign.
Using the Flexible MROUND Function Suppose you want to round a number to a multiple of something other than 10—for example, rounding numbers to sixteenths so that when formatted as fractions they never appear with a denominator larger than 16. The MROUND function rounds any number to a multiple you specify.
The function takes the form =MROUND(number, multiple). For example, typing the formula =MROUND(A1, .0625) rounds the number displayed in cell A1 in increments of one-sixteenth. The function rounds up if the remainder after dividing number by multiple is at least half the value of multiple. If you want to apply this to an existing formula, just wrap the MROUND formula around it by replacing A1 (in the example) with your formula.
The INT Function
The INT function rounds numbers down to the nearest integer. For example, the formulas
=INT(100.01)
=INT(100.99999999)
both return the value 100, even though the number 100.99999999 is essentially equal to 101. When a number is negative, INT also rounds that number down to the next integer. If each of the numbers in these examples were negative, the resulting value would be –101.
The TRUNC Function
The TRUNC function truncates everything to the right of the decimal point in a number, regardless of its sign. It takes the arguments (number, num_digits). If num_digits isn’t specified, it’s set to 0. Otherwise, TRUNC truncates everything after the specified number of digits to the right of the decimal point. For example, the formula =TRUNC(13.978) returns the value 13; the formula =TRUNC(13.978, 1) returns the value 13.9.
AVERAGE vs. AVG Some other spreadsheet programs use the AVG statistical function to compute averages. In some previous versions of Excel, typing the formula =AVG(2, 4, 5, 8) would result in a #NAME? error. Excel now accepts AVG, although when you type the function, an error dialog box appears, asking whether you want to change the function to AVERAGE. That’s still kind of rude, but it works. Presumably, one reason why Excel doesn’t just change AVG to AVERAGE for you is to remind you to start using the correct function name.
When you use this function, Excel ignores cells containing text, logical values, or empty cells, but it includes cells containing a zero value. You can also choose the AVERAGEA function, which operates in the same way as AVERAGE, except it includes text and logical values in the calculation.
What do you think of this book? We want to hear from you! Microsoft is interested in hearing your feedback so we can continually improve our books and learning resources for you. To participate in a brief online survey, please visit: