Name
|
Description
|
Syntax
|
ABS
|
|
|
|
Returns the absolute value of a number. The absolute value of a number is the number without its sign.
|
ABS(number)
|
ACOS
|
|
|
|
Returns the arccosine, or inverse cosine, of a number. The returned angle is given in radians in the range 0 (zero) to pi.
|
ACOS(number)
The argument must be from -1 to 1.
|
ACOSH
|
|
|
|
Returns the inverse hyperbolic cosine of a number. The number must be greater than or equal to 1.
|
ACOSH(number)
|
ACOT
|
|
|
|
Returns the inverse cotangent (the arccotangent) of a number.
|
ACOT(number)
|
ACOTH
|
|
|
|
Returns the inverse hyperbolic cotangent of a number.
|
ACOTH(number)
|
AGGREGATE
|
|
|
|
Returns an aggregate in a list or database.
|
Reference form: AGGREGATE(function_num, options, ref1, [ref2], …)
Array form: AGGREGATE(function_num, options, array, [k])
The function_num argument specifies which function to use.
function_num
|
Function
|
Form
|
1
|
AVERAGE
|
Reference
|
2
|
COUNT
|
Reference
|
3
|
COUNTA
|
Reference
|
4
|
MAX
|
Reference
|
5
|
MIN
|
Reference
|
6
|
PRODUCT
|
Reference
|
7
|
STDEV.S
|
Reference
|
8
|
STDEV.P
|
Reference
|
9
|
SUM
|
Reference
|
10
|
VAR.S
|
Reference
|
11
|
VAR.P
|
Reference
|
12
|
MEDIAN
|
Reference
|
13
|
MODE.SNGL
|
Reference
|
14
|
LARGE
|
Array
|
15
|
SMALL
|
Array
|
16
|
PERCENTILE.INC
|
Array
|
17
|
QUARTILE.INC
|
Array
|
18
|
PERCENTILE.EXC
|
Array
|
19
|
QUARTILE.EXC
|
Array
|
The options argument defines which values to ignore during calculation.
Options
|
Behavior
|
0 or omitted
|
Ignore nested SUBTOTAL and AGGREGATE functions.
|
1
|
Ignore hidden rows, nested SUBTOTAL and AGGREGATE functions.
|
2
|
Ignore error values, nested SUBTOTAL and AGGREGATE functions.
|
3
|
Ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions.
|
4
|
Ignore nothing.
|
5
|
Ignore hidden rows.
|
6
|
Ignore error values.
|
7
|
Ignore hidden rows and error values.
|
Ref1, [ref2], … specify numeric arguments for the function when the reference form is used (you can supply up to 253 ref arguments).
The array argument specifies an array, an array formula, or a reference to a cell range when the array form is used. [k] is the second argument required for the following functions: LARGE, SMALL, PERCENTILE.INC, QUARTILE.INC, PERCENTILE.EXC, and QUARTILE.EXC.
|
ARABIC
|
|
|
|
Converts a Roman numeral to an Arabic numeral.
|
ARABIC(text)
|
ASIN
|
|
|
|
Returns the arcsine, or inverse sine, of a number. The returned angle is given in radians in the range -pi/2 to pi/2.
|
ASIN(number)
The argument must be in the range of -1 to 1.
|
ASINH
|
|
|
|
Returns the inverse hyperbolic sine of a number.
|
ASINH(number)
|
ATAN
|
|
|
|
Returns the arctangent, or inverse tangent, of a number. The returned angle is given in radians in the range -pi/2 to pi/2.
|
ATAN(number)
|
ATAN2
|
|
|
|
Calculates the arctangent (or inverse tangent) of the specified x- and y-coordinates. The returned angle is given in radians between -pi and pi, excluding -pi.
|
ATAN2(x_num, y_num)
|
ATANH
|
|
|
|
Returns the inverse hyperbolic tangent of a number. The number must be between -1 and 1 (excluding -1 and 1).
|
ATANH(number)
|
BASE
|
|
|
|
Converts a number into text representation with a given base.
|
BASE(number, radix [min_length])
The number must be a positive integer less than 2^53. Radix is the base that you want to convert the number into. Must be an integer greater than or equal to 2 and less than or equal to 36. Min_length is the minimum length of the returned string. If this parameter is specified, leading zeros are added to the result if necessary.
|
CEILING
|
|
|
|
Returns a number rounded up, away from zero, to the nearest multiple of significance.
|
CEILING(number, significance)
The number is the value to round, the significance is the multiple to which you want to round. If the number is negative, and the significance is negative, the value is rounded down, away from zero. If the number is negative, and the significance is positive, the value is rounded up towards zero.
|
CEILING.MATH
|
|
|
|
Rounds a number up to the nearest integer or to the nearest multiple of significance.
|
CEILING.MATH(number, [significance], [mode])
The number is the value to round, the optional significance parameter is the multiple to which you want to round. The optional mode parameter affects negative numbers only and specifies whether the number is rounded toward or away from zero.
|
CEILING.PRECISE
|
|
|
|
Returns a number that is rounded up to the nearest integer or to the nearest multiple of significance.
|
CEILING.PRECISE(number, [significance])
|
COMBIN
|
|
|
|
Returns the number of combinations for a given number of items.
|
COMBIN(number, number_chosen)
Number is the total number of items, and number_chosen is the number of items in each combination.
|
COMBINA
|
|
|
|
Returns the number of combinations (with repetitions) for a given number of items.
|
COMBINA(number, number_chosen)
Number is the total number of items, and number_chosen is the number of items in each combination.
|
COS
|
|
|
|
Returns the cosine of an angle specified in radians.
|
COS(number)
|
COSH
|
|
|
|
Returns the hyperbolic cosine of a number.
|
COSH(number)
|
COT
|
|
|
|
Returns the cotangent of an angle specified in radians.
|
COT(number)
|
COTH
|
|
|
|
Return the hyperbolic cotangent of a number.
|
COTH(number)
|
CSC
|
|
|
|
Returns the cosecant of an angle specified in radians.
|
CSC(number)
|
CSCH
|
|
|
|
Returns the hyperbolic cosecant of an angle specified in radians.
|
CSCH(number)
|
DECIMAL
|
|
|
|
Converts text representation of a number in a given base into a decimal number.
|
DECIMAL(text, radix)
|
DEGREES
|
|
|
|
Converts radians into degrees.
|
DEGREES(angle)
|
EVEN
|
|
|
|
Returns a number rounded up to the nearest even integer.
|
EVEN(number)
Regardless of the sign of the number, the value is rounded up when adjusted away from zero. If the number is an even integer, no rounding occurs.
|
EXP
|
|
|
|
Returns e raised to the power of number. The constant e equals 2.71828, the base of the natural logarithm.
|
EXP(number)
|
FACT
|
|
|
|
Returns the factorial of a number.
|
FACT(number)
The number is nonnegative. If the number is not an integer, it is truncated.
|
FACTDOUBLE
|
|
|
|
Returns the double factorial of a number.
|
FACTDOUBLE(number)
|
FLOOR
|
|
|
|
Rounds a number down, toward zero, to the nearest multiple of significance.
|
FLOOR(number, significance)
The number is the value to round, and the significance is the multiple to which you want to round.
|
FLOOR.MATH
|
|
|
|
Rounds a number down to the nearest integer or to the nearest multiple of significance.
|
FLOOR.MATH(number, [significance], [mode])
The number is the value to round, and the significance is the multiple to which you want to round. The optional mode parameter specifies the direction (toward or away from 0) to round negative numbers).
|
FLOOR.PRECISE
|
|
|
|
Rounds a number down to the nearest integer or to the nearest multiple of significance. Regardless of the sign of the number, the number is rounded down.
|
FLOOR.PRECISE(number, [significance])
|
GCD
|
|
|
|
Returns the greatest common divisor of two or more integers.
|
GCD(number1, [number2], …)
|
INT
|
|
|
|
Rounds a number down to the nearest integer.
|
INT(number)
|
ISO.CEILING
|
|
|
|
Returns a number that is rounded up to the nearest integer or to the nearest multiple of significance.
|
ISO.CEILING(number, [significance])
The number is the value to round, and the optional significance is the multiple to which you want to round. If the significance is omitted, the default value is 1.
|
LCM
|
|
|
|
Returns the least common multiple of integers.
|
LCM(number1, [number2], …)
|
LN
|
|
|
|
Returns the natural logarithm of a number.
|
LN(number)
The number is the positive real number.
|
LOG
|
|
|
|
Returns the logarithm of a number to the base you specify.
|
LOG(number, [base])
The number is the positive real number. The base parameter is the base of the logarithm. If it is omitted, the base is assumed to be 10.
|
LOG10
|
|
|
|
Returns the base-10 logarithm of a number.
|
LOG10(number)
The number is the positive real number.
|
MDETERM
|
|
|
|
Returns the matrix determinant of an array.
|
MDETERM(array)
|
MINVERSE
|
|
|
|
Returns the inverse matrix for a matrix stored in an array.
|
MINVERSE(array)
|
MMULT
|
|
|
|
Returns the matrix product of two arrays. The result is an array with the same number of rows as array1 and the same number of columns as array2.
|
MMULT(array1, array2)
The number of columns in array1 must be the same as the number of rows in array2, and both arrays must contain only numbers.
|
MOD
|
|
|
|
Returns the remainder after a number is divided by a divisor.
|
MOD(number, divisor)
The number is the number for which to find the remainder, and the divisor is the number by which you want to divide the number. The result has the same sign as the divisor.
|
MROUND
|
|
|
|
Returns a number rounded to the desired multiple.
|
MROUND(number, multiple)
The number is the value to round, and the second parameter is the multiple to which you want to round the number. The function rounds up, away from zero, if the remainder of dividing the number by multiple is greater than or equal to half the value of the multiple.
|
MUNIT
|
|
|
|
Returns the unit matrix for the specified dimension.
|
MUNIT(dimension)
|
MULTINOMIAL
|
|
|
|
Returns the multinomial of a set of numbers.
|
MULTINOMIAL(number1, [number2], …)
|
ODD
|
|
|
|
Rounds a number up to the nearest odd integer.
|
ODD(number)
|
PI
|
|
|
|
Returns the number 3.14159265358979, the mathematical constant pi, accurate to 15 digits.
|
PI()
|
POWER
|
|
|
|
Returns the result of a number raised to a power.
|
POWER(number, power)
The number is a real number, and the power is the exponent to which the number is raised. You can use the “^” operator instead.
|
PRODUCT
|
|
|
|
Multiplies all numbers given as arguments and returns the product.
|
PRODUCT(number1, [number2], …)
If an argument is an array or reference, only numbers in the array or reference are multiplied. Empty cells, logical values, and text are ignored.
|
QUOTIENT
|
|
|
|
Returns the integer portion of a division.
|
QUOTIENT(numerator, denominator)
|
RADIANS
|
|
|
|
Converts degrees to radians.
|
RADIANS(angle)
|
RAND
|
|
|
|
Returns an evenly distributed random real number greater than or equal to 0 and less than 1. A new random real number is returned every time the worksheet is calculated.
|
RAND()
|
RANDBETWEEN
|
|
|
|
Returns a random integer number between the numbers you specify. A new random integer number is returned every time the worksheet is calculated.
|
RANDBETWEEN(bottom, top)
|
ROMAN
|
|
|
|
Converts an arabic numeral to roman, as text.
|
ROMAN(number, [form])
|
ROUND
|
|
|
|
Rounds a number to a specified number of digits.
|
ROUND(number, num_digits)
If num_digits is 0, the number is rounded to the nearest integer. If num_digits is greater than 0, the number is rounded to the specified number of decimal places. If num_digits is less than 0, the number is rounded to the left of the decimal point.
|
ROUNDDOWN
|
|
|
|
Rounds a number down (toward zero) to a specified number of digits.
|
ROUNDDOWN(number, num_digits)
If num_digits is 0, the number is rounded down to the nearest integer. If num_digits is greater than 0, the number is rounded down to the specified number of decimal places. If num_digits is less than 0, the number is rounded down to the left of the decimal point.
|
ROUNDUP
|
|
|
|
Rounds a number up (away from zero) to a specified number of digits.
|
ROUNDUP(number, num_digits)
If num_digits is 0, the number is rounded up to the nearest integer. If num_digits is greater than 0, the number is rounded up to the specified number of decimal places. If num_digits is less than 0, the number is rounded up to the left of the decimal point.
|
SEC
|
|
|
|
Returns the secant of an angle specified in radians.
|
SEC(number)
|
SECH
|
|
|
|
Returns the hyperbolic secant of an angle specified in radians.
|
SECH(number)
|
SERIESSUM
|
|
|
|
Returns the sum of a power series based on the formula.
|
SERIESSUM(x, n, m, coefficients)
|
SIGN
|
|
|
|
Determines the sign of a number. Returns 1 if the number is positive, zero (0) if the number is 0, and -1 if the number is negative.
|
SIGN(number)
|
SIN
|
|
|
|
Returns the sine of an angle specified in radians.
|
SIN(number)
|
SINH
|
|
|
|
Returns the hyperbolic sine of a number.
|
SINH(number)
|
SQRT
|
|
|
|
Returns a positive square root.
|
SQRT(number)
|
SQRTPI
|
|
|
|
Multiplies a specified number by pi and returns the square root of the product.
|
SQRTPI(number)
|
SUBTOTAL
|
|
|
|
Returns a subtotal in a list or database.
|
SUBTOTAL(function_num,ref1,[ref2],…)
Function_num is the number that specifies the calculation type. Possible values for the function_num argument are listed in the following table:
function_num (include hidden values)
|
function_num (ignore hidden values)
|
Function
|
1
|
101
|
AVERAGE
|
2
|
102
|
COUNT
|
3
|
103
|
COUNTA
|
4
|
104
|
MAX
|
5
|
105
|
MIN
|
6
|
106
|
PRODUCT
|
7
|
107
|
STDEV
|
8
|
108
|
STDEVP
|
9
|
109
|
SUM
|
10
|
110
|
VAR
|
11
|
111
|
VARP
|
|
SUM
|
|
|
|
Adds all numbers that you specify as arguments.
|
SUM(number1,[number2],…)
Each argument can be a range, a cell reference, an array, a constant, a formula, or the result from another function. If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, or text are ignored.
|
SUMIF
|
|
|
|
Adds cells specified by a given condition or criterion.
|
SUMIF(range, criteria, [sum_range])
Range is the range of cells you want evaluated by criteria. Criteria is a number, expression, cell reference, text, or function that defines which cells to add. The optional sum_range parameter specifies the actual cells to add. If it is omitted, cells specified in the range argument are added.
|
SUMIFS
|
|
|
|
Sums all cells that meet multiple criteria.
|
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
Sum_range is the range of cells to sum. Criteria_range is the cell range in which to evaluate the specified criteria. The criteria is a number, expression, cell reference or text that defines which cells in the criteria_range argument should be added.
|
SUMPRODUCT
|
|
|
|
Returns the sum of the products of the corresponding ranges or arrays.
|
=SUMPRODUCT(array1, [array2], [array3], …)
The array arguments must have the same dimensions. Array entries that are not numeric are treated as zeros.
|
SUMSQ
|
|
|
|
Returns the sum of the squares of the arguments.
|
SUMSQ(number1, number2, …)
You can also use a single array or a reference to an array instead of arguments separated by commas. Empty cells, logical values, text, or error values in the array or reference are ignored.
|
SUMX2MY2
|
|
|
|
Returns the sum of the difference of squares of corresponding values in two arrays.
|
SUMX2MY2(array_x, array_y)
|
SUMX2PY2
|
|
|
|
Returns the sum of the sum of squares of corresponding values in two arrays.
|
SUMX2PY2(array_x, array_y)
|
SUMXMY2
|
|
|
|
Returns the sum of squares of differences of corresponding values in two arrays.
|
SUMXMY2(array_x, array_y)
|
TAN
|
|
|
|
Returns the tangent of an angle specified in radians.
|
TAN(angle)
|
TANH
|
|
|
|
Returns the hyperbolic tangent of a number.
|
TANH(number)
|
TRUNC
|
|
|
|
Truncates a number to an integer by removing the fractional part of the number.
|
TRUNC(number, [num_digits])
|
|