Using Selected Mathematical Functions

USING SELECTED MATHEMATICAL FUNCTIONS

Excel has 58 built-in math and trig functions. This section only brushes the surface, covering a few of the more useful or misunderstood functions.

The PRODUCT and SUMPRODUCT Functions

The PRODUCT function multiplies all its arguments and can take as many as 30 arguments that are text or logical values; blank cells are ignored.

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 30 arrays as arguments but each array must have the same dimensions. (Nonnumeric 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 the SUM formula must be entered as an array by pressing Ctrl+Shift+Enter.

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

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. Note that you must still type the parentheses after the function name. The result of a RAND function changes each time you recalculate your worksheet. If you use automatic recalculation, the value of the RAND function changes each time you make a worksheet entry.

The RANDBETWEEN function is available when you install the Analysis ToolPak add-in, 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.