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.
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.
Note
You can create powerful conditional SUM formulas using add-in tools. See "Using the Conditional Sum and Lookup Wizards".
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.
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 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.
Note
For more information about arrays, see "Using Arrays".
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 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 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 RAND-BETWEEN, 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.
Excel includes several functions devoted to the seemingly narrow task of rounding numbers by a specified amount.
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.
Note
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 change the actual values of numbers.
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 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 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 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.