Microsoft Office Tutorials and References

In Depth Information

**Using selected mathematical functions**

array, k)
, where
array
is the array of values you want to aggregate and, depending on the

function,
k
is a number indicating the percentile, quartile, or
k
th largest or
k
th smallest

value you want to find.

TABLE 14-2
AGGREGATE options

Argument
Description

0 or omitted

Ignore nested SUBTOTAL and AGGREGATE functions

Ignore hidden rows, nested SUBTOTAL and AGGREGATE functions

1

Ignore error values, nested SUBTOTAL and AGGREGATE functions

2

Ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE

functions

3

Ignore nothing

4

Ignore hidden rows

5

Ignore error values

6

Ignore hidden rows and error values

7

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” in Chapter 12.

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.