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
0 or omitted
Ignore nested SUBTOTAL and AGGREGATE functions
Ignore hidden rows, nested SUBTOTAL and AGGREGATE functions
Ignore error values, nested SUBTOTAL and AGGREGATE functions
Ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE
Ignore hidden rows
Ignore error values
Ignore hidden rows and error values
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:
The only difference between them is that you must enter the SUM formula as an array by
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.