Mathematical Functions
- 13 minutes to read
This document briefly describes mathematical functions implemented in the ASPxSpreadsheet.
ABS(number)
- Returns the absolute value of a number. The absolute value of a number is the number without its sign.
ACOS(number)
Returns the arccosine, or inverse cosine, of a number. The returned angle is given in radians in the range of -pi/2 to pi/2.
The argument must be in the range of -1 to 1.
ACOSH(number)
- Returns the inverse hyperbolic cosine of a number.
ACOT(number)
- Returns the inverse cotangent (the arccotangent) of a number.
ACOTH(number)
- Returns the inverse hyperbolic cotangent of the number.
AGGREGATE(function_num, options, ref1, [ref2], …)
/AGGREGATE(function_num, options, array, [k])
Returns an aggregate in a list or database.
The
function_num
argument specifies the function to use in the calculation. Possible values for thefunction_num
argument are listed in the following table: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 is a number that defines which values to ignore during the 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 a second argument required for the following functions: LARGE, SMALL, PERCENTILE.INC, QUARTILE.INC, PERCENTILE.EXC, and QUARTILE.EXC.ARABIC(text)
- Converts a Roman numeral to an Arabic numeral.
ASIN(number)
Returns the arcsine, or inverse sine, of a number. The returned angle is given in radians in the range of -pi/2 to pi/2.
The argument must be in the range of -1 to 1.
ASINH(number)
- Returns the inverse hyperbolic sine of a number
ATAN(number)
- Returns the arctangent, or inverse tangent, of a number. The returned angle is given in radians in the range -pi/2 to pi/2.
ATAN2( x_num, y_num )
- Calculates the arctangent (i.e. the inverse tangent) of a pair of x and y coordinates, and returns an angle, in radians.
ATANH(number)
- Returns the inverse hyperbolic tangent of a given number.
BASE(number, radix, min_length)
Converts a number into a text representation with the given base.
The
number
must be a positive integer less than 2^53.The
radix
is the base into what the number is converted. The radix is an integer >= 2 and <= 36.min_length
is optional, it is the minimum length of the returned string. If the Min_length parameter is present, leading zeros are added if required.CEILING(number, significance)
Returns a number rounded up, away from zero, to the nearest multiple of significance.
The
number
is the value to round, thesignificance
is the multiple to which you want to round. If thenumber
is negative, and thesignificance
is negative, the value is rounded down, away from zero. If thenumber
is negative, and thesignificance
is positive, the value is rounded up towards zero.CEILING.MATH(number, significance, mode)
Rounds a
number
the nearest integer or to the nearest multiple ofsignificance
. Regardless of the sign of the number, the number is rounded up.The
number
is the value to round, the optionalsignificance
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 zero (mode
= 0) or away from zero (mode
<> 0).CEILING.PRECISE(number, significance )
- Returns number rounded up to the nearest integer or to the nearest multiple of significance.
COMBIN( n, k )
Returns the number of combinations for a given number of items.
n
is the number of items in the set,k
is the number of items to choose from the set.COMBINA( n, k )
Returns the number of combinations (with repetitions) for a given number of items.
n
is the number of items in the set,k
is the number of items to choose from the set.COS(number)
Returns the cosine of the given angle.
The
number
is the angle in radians.COSH(number)
Returns the hyperbolic cosine of a number.
The
number
is any real number.COT(number)
- Returns the cotangent of an angle.
COTH(number)
- Returns the hyperbolic cotangent of a number.
CSC(number)
- Returns the cosecant of an angle.
CSCH(number)
- Returns the hyperbolic cosecant of an angle.
DECIMAL(text, radix)
- Converts a text representation of a number in a given base into a decimal number.
DEGREES(angle)
Converts radians into degrees.
The
angle
is in radians.EVEN(number)
Returns a number rounded up to the nearest even integer.
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(number)
- Returns the value of the mathematical constant e raised to the power of the number.
FACT(number)
Returns the factorial of a number.
The
number
is nonnegative. It the number is not an integer, it is truncated.FACTDOUBLE(number)
- Returns the double factorial of a number.
FLOOR(number, significance)
Rounds the number down toward zero, to the nearest multiple of significance.
The
number
is the value to round, thesignificance
is the multiple to which you want to round.FLOOR.MATH(number, significance, mode)
Rounds a number down, to the nearest integer or to the nearest multiple of significance.
The
number
is the value to round, the optionalsignificance
is the multiple to which you want to round, the optionalmode
specifies the direction to round negative numbers (toward 0 if equal to 0, away from 0 if themode
is not zero).FLOOR.PRECISE(number, significance )
- 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.
GCD(number1, [number2], ...)
- Returns the greatest common divisor.
INT(number)
- Rounds a number down to the nearest integer.
ISO.CEILING(number, significance)
Returns a number that is rounded up to the nearest integer or to the nearest multiple of significance.
The
number
is the value to round, the optionalsignificance
is the multiple to which you want to round.LN(number)
Returns the natural logarithm of a number.
The
number
is a positive real number.LCM(number1, [number2], ...)
- Returns the least common multiple.
LOG(number, base)
Returns the logarithm of a number to the base that you specify.
The
number
is a positive real number (thebase
is optional). It is thebase
of the logarithm. If omitted, thebase
is assumed to be 10.LOG10(number)
Returns the base-10 logarithm of a number.
The
number
is a positive real number.MDETERM(array)
- Returns the matrix determinant of an array.
MINVERSE(array)
- Returns the matrix inverse of an array.
MMULT(array1, array2)
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.
The number of columns in array1 must be the same as the number of rows in array2, and both arrays must only contain numbers.
MOD(number, divisor)
Returns the remainder after a number is divided by a divisor.
The
number
is the number for which to find the remainder, and thedivisor
is the number by which you want to divide the number. The result has the same sign as the divisor.MROUND(number, multiple)
Returns a number rounded to the desired multiple.
The
number
is the value to round, themultiple
is the value 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. For example, MROUND(10,3) returns 9 because the remainder of 10 divided by 3 is 1 which is less than half of 3. MROUND(11,3) returns 12.MUNIT(dimension)
- Returns the unit matrix or the specified dimension.
MULTINOMIAL(number1, [number2], ...)
- Returns the multinomial of a set of numbers.
ODD(number)
- Rounds a number up to the nearest odd integer.
PI()
- Returns the number 3.14159265358979, the mathematical constant pi, accurate to 15 digits.
POWER(number, power)
Returns the result of a number raised to a power.
The
number
is a real number, and thepower
is the exponent to which the number is raised. You can use the “^” operator instead.PRODUCT(number1, number2, ...)
Multiplies all numbers given as arguments and returns the product.
If an argument is an array or reference, only numbers in the array or reference are multiplied. Empty cells, logical values, and text in the array or reference are ignored.
QUOTIENT(number, divisor)
- Returns the integer portion of a division.
RADIANS(angle)
- Converts degrees to radians.
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.
RANDBETWEEN(bottom, top)
Returns a random integer number between the numbers that are specified. A new random integer number is returned every time the worksheet is calculated.
The bottom is the smallest integer, the top is the largest integer that the function will return.
ROMAN(number, [form])
- Converts an arabic numeral to roman, as text.
ROUND(number, num_digits)
Rounds a number to a specified number of digits.
If
num_digits
is 0, the number is rounded to the nearest integer. Ifnum_digits
is greater than 0 (zero), then the number is rounded to the specified number of decimal places. If num_digits is less than 0, then the number is rounded to the left of the decimal point.ROUNDDOWN(number, num_digits)
Rounds a number down, toward zero, to a specified number of digits.
If
num_digits
is 0, thenumber
is rounded to the nearest integer. Ifnum_digits
is greater than 0 (zero), then thenumber
is rounded to the specified number of decimal places. Ifnum_digits
is less than 0, then thenumber
is rounded to the left of the decimal point.ROUNDUP(number, num_digits)
Rounds a number up to a specified number of digits.
If
num_digits
is 0, thenumber
is rounded to the nearest integer. Ifnum_digits
is greater than 0 (zero), then thenumber
is rounded to the specified number of decimal places. Ifnum_digits
is less than 0, then thenumber
is rounded to the left of the decimal point.SEC(number)
- Returns the secant of an angle.
SECH(number)
- Returns the hyperbolic secant of an angle.
SERIESSUM(x, n, m, coefficients)
- Returns the sum of a power series based on the formula.
SIGN(number)
- 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.
SIN(number)
Returns the sine of the given angle.
The
number
is the angle in radians.SIN()
Returns the hyperbolic sine of a number.
The
number
is the angle in radians.SQRT(number)
Returns a positive square root.
The
number
is any positive number.SQRTPI(number)
Multiplies a specified number by pi and returns a square root of the product.
The
number
is any positive number.SUBTOTAL(function_num, ref1, ref2, ...)
Performs a specified calculation (the sum, product, average, etc.) for a supplied set of values
The
function_num
argument is a number that specifies the calculation type. Possible values for thefunction_num
argument are listed in the following table. Note that by selecting a properfunction_num
argument you can ignore or include hidden values in calculation.
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(number1, number2, ...)
Adds all numbers that you specify as arguments
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 in the array or reference are ignored.
SUMIF(range, criteria, sum_range)
Sums the values in a range that meet criteria that you specify
range
is the range of cells that you wish to filter by criteria.criteria
is a number, expression, cell reference, text, or function that defines which cells will be added. Optionalsum_range
specifies the actual cells to add. If omitted, the cells specified in the Range argument are added.SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)
Sums the cells in a range that meet multiple criteria.
sum_range
is the range of cells to sum. Criteria_range specifies the 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 will be added. You can specify several criteria ranges with specific criteria for each.SUMPRODUCT(array1, array2, array3, ...)
Returns the sum of the products of the corresponding values in two or more supplied arrays.
The array arguments must have the same dimensions. Array entries that are not numeric are treated as if they were zeros.
SUMSQ(number1, number2, ...)
Returns the sum of the squares of the arguments.
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(array_x, array_y)
- Returns the sum of the difference of squares of corresponding values in two arrays.
SUMX2PY2(array_x, array_y)
- Returns the sum of the sum of squares of corresponding values in two arrays.
SUMXMY2(array_x, array_y)
Returns the sum of squares of differences of corresponding values in two arrays
array_x
andarray_y
should have the same number of values. If an array or reference argument contains text, logical values, or empty cells, those values are ignored.TAN(angle)
Returns the tangent of the given angle.
The
angle
is measured in radians.TANH(number)
Returns the hyperbolic tangent of a number.
The
number
is any real number.TRUNC(number, num_digits)
Truncates a number to a specified number of decimal places.
If an optional parameter
num_digits
is omitted or has a value of 0, rounding is performed on an integer.