2007 MicrosoftВ® Office System Inside Out (Bpg-Inside Out)

Most of the work you do in Excel will probably involve 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’ll 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. These buttons also include a menu of commonly used functions, including SUM. 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.

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 new 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 new, 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 also works if, rather than 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.

This 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 involves editing the referencing formulas manually.

Using Selected Mathematical Functions

Excel has 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 434.

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.

Here’s a practical use of the MOD function that you can ponder:

1. Select a range of cells such as A1:G12, 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.

This 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).

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 12-player football teams you can create from a pool of 17 players, type the formula =COMBIN(17, 12). The result indicates that you could create 6,188 teams.

The COMBIN function can help you figure out just how slim a chance you have of getting the elusive ace-high straight flush in a game of five-card stud. You express the number of card combinations 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. To figure that out, you need to know the number of possible combinations when choosing 6 numbers out of a total of 49. Type the formula =COMBIN(49, 6), and the result is 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.

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, rounding digits less than 5 down and digits greater than or equal to 5 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.

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.

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 the 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.

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 so you will learn to start using the correct function name.

When you use this function, Excel ignores cells containing text, logical values, or empty cells but 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.