Online sheet  ->  公式  ->  Numeric / Mathematical Functions

Numeric / Mathematical Functions


Formulas Description Examples
ABS Syntax: ABS(number)
Returns the absolute value of the given number.
=ABS(A1)
ACOS Syntax: ACOS(number)
Returns the arccosine, or inverse cosine, of a number. The arccosine is the angle whose cosine is number. The returned angle is given in radians in the range 0 (zero) to pi. Number is the cosine of the angle and must be from -1 to 1.
=ACOS(0.3)
=ACOS(A1)
ACOSH Syntax: ACOSH(number)
Returns the inverse hyperbolic cosine of a number. Number must be greater than or equal to 1.
=ACOSH(3)
=ACOSH(A1)
ACOT Syntax: ACOT(number)
Returns the inverse hyperbolic tangent of a number. Number must be between -1 and 1.
=ACOT(0.3)
=ACOT(A1)
ASIN Syntax: ASIN(number)
Returns the arcsine, or inverse sine, of a number. The arcsine is the angle whose sine is number. The returned angle is given in radians in the range -pi/2 to pi/2. Number is the sine of the angle and must be from -1 to 1.
=ASIN(5)
=ASIN(A1)
ASINH Syntax: ASINH(number)
Returns the inverse hyperbolic sine of a number.
=ASINH(1)
=ASINH(A1)
ATAN Syntax: ATAN(number)
Returns the arctangent, or inverse tangent, of a number. The arctangent is the angle whose tangent is number. The returned angle is given in radians in the range -pi/2 to pi/2. Number is the tangent of the angle and must be from -1 to 1.
=ATAN(1)
=ATAN(A1)
ATAN2 Syntax: ATAN2(x_num, y_num)
Returns the arctangent, or inverse tangent, of the specified x- and y-coordinates.
=ATAN2(3,5)
ATANH Syntax: ATANH(number)
Returns the inverse hyperbolic tangent of a number. Number must be between -1 and 1.
=ATANH(0.5)
=ATANH(A1)
CEILING Syntax: CEILING(number, significance)
Returns number rounded up, away from zero, to the nearest multiple of significance.
=CEILING(A5, 1)
=CEILING(210, 0.05)
COMBIN Syntax: COMBIN(number, CHOOSE)
Returns the number of combinations for a given number of items. Use COMBIN to determine the total possible number of groups for a given number of items.
Number is the number of items.
Number_chosen is the number of items in each combination.
=COMBIN(A5, 1)
=COMBIN(210, 0.05)
COMBINA Syntax: COMBINA(number, CHOOSE)
Returns the number of combinations (with repetitions) for a given number of items.
=COMBINA(A5, 1)
=COMBINA(10, 3)
COS Syntax: COS(number)
Returns a Double specifying the cosine of an angle. Number is a Double or any valid numeric expression that expresses an angle in radians.
=COS(A1)
=COS(5)
=COS(-0.5)
COT Syntax: COT(number)
Returns the hyperbolic cosine of a number.
=COS(A1)
=COS(5)
COSH Syntax: COSH(number)
Returns the hyperbolic cosine of a number.
=COSH(A1)
=COSH(5)
=COSH(-0.5)
CSC Syntax: CSC(number)
Returns the cosecant of an angle specified in radians.
=CSC(A1)
=CSC(15)
CSCH Syntax: CSCH(number)
Return the hyperbolic cosecant of an angle specified in radians.
=CSCH(A1)
=CSCH(15)
DEGREES Syntax: DEGREES(number)
This function converts radians into degrees.
=DEGREES(A1)
=DEGREES(30)
EXP Syntax: EXP(number)
Returns a Double specifying e (the base of natural logarithms) raised to a power.
=EXP(A1)
=EXP(10)
EVEN Syntax: EVEN(number)
Returns number rounded up to the nearest even integer.
=EXP(A1)
=EVEN(5.5)
FACT Syntax: FACT(number)
Returns the factorial of a number. The factorial of a number is equal to 1*2*3*...* number.
=FACT(A1)
=FACT(1.9)
FACTDOUBLE Syntax: FACTDOUBLE(number)
Returns the double factorial of a number.
=FACTDOUBLE(A1)
=FACTDOUBLE(6)
FLOOR Syntax: FLOOR(number, significance)
Rounds number down, toward zero, to the nearest multiple of significance.
=FLOOR(A1, 0.01)
=FLOOR(0.234, 0.01)
GCD Syntax: GCD(number1, number2, ...)
Returns the greatest common divisor of two or more integers. The greatest common divisor is the largest integer that divides both number1 and number2 without a remainder.
=GCD(A1,A2)
=GCD(24, 36 )
INT Syntax: INT(number)
Rounds a number down to the nearest integer.
=INT(a1)
=INT(-5.5)
LCM Syntax: LCM(number1, number2, ...)
Calculate the Least Common Multiple, which is the smallest number that can be divided by each of the given numbers.
=LCM(a1,a2,a3....)
LN Syntax: LN(number)
Returns the natural logarithm of a number. Natural logarithms are based on the constant e (2.71828182845904).
=LN(2)
LOG Syntax: LOG(number, [base])
Returns the logarithm of a number to the base you specify.
=LOG(2, 3)
LOG10 Syntax: LOG10(number)
Returns the base-10 logarithm of a number.
=LOG10(10^5)
MOD Syntax: MOD(number,divisor)
Mode returns the remainder after number is divided by divisor. The result has the same sign as divisor.
=MOD(-3, 2)
=MOD(3, 2)
=MOD(A2, 3)
MROUND Syntax: MROUND(number, multiple)
Returns a number rounded to the desired multiple.
=MROUND(10, 3)
=MROUND(5, -2)
ODD Syntax: ODD(number)
Returns number rounded up to the nearest odd integer.
=ODD(2)
=ODD(6.5)
=ODD(A2)
PI Syntax: PI()
Returns the value of Pi, 3.14159265358979.
=PI()
POWER Syntax: POWER(base, power)
Returns the result of a number raised to a power. Base is the number that is to be raised to the given power. Power is the exponent by which the base is to be raised.
=POWER(a1,2)
=POWER(a1,a2)
=POWER(5,2)
PRODUCT Syntax: PRODUCT(number1, number2, ...)
Multiplies its arguments
=PRODUCT(a1,a2,a3)
=PRODUCT(a1:a3)
QUOTIENT Syntax: QUOTIENT(numerator,denominator)
Returns the integer portion of a division. Use this function when you want to discard the remainder of a division.
=QUOTIENT(A1, A2)
=QUOTIENT(4.5, 3.1)
RADIANS Syntax: RADIANS(angle)
Converts degrees to radians.
=RADIANS(a1)
=RADIANS(170)
RAND Syntax: RAND()
Returns a random number between 0 and 1.
=RAND()
RANDBETWEEN Syntax: RANDBETWEEN(bottom,top)
Returns a random number between the numbers you specify.
=RANDBETWEEN(A1,A2)
=RANDBETWEEN(1,100)
ROUND Syntax: ROUND(number, count)
Rounds the given number to a certain number of decimal places according to valid mathematical criteria. Count (optional) is the number of the places to which the value is to be rounded. If the count parameter is negative, only the whole number portion is rounded. It is rounded to the place indicated by the count.
=ROUND(a1,2)
=ROUND(-78.96,1)
=ROUND(55.1,-1)
ROUNDDOWN Syntax: ROUNDDOWN(number, count)
Rounds the given number to a certain number of decimal places according to valid mathematical criteria (toward zero). Count is the number of the places to which the value is to be rounded.
=ROUNDDOWN(A1,5)
=ROUNDDOWN(36.8,0)
=ROUNDDOWN(31415.92654, -3)
ROUNDUP Syntax: ROUNDUP(number, count)
Rounds the given number to a certain number of decimal places according to valid mathematical criteria (away zero). Count is the number of the places to which the value is to be rounded.
=ROUNDUP(A1,2)
=ROUNDUP(36.8,0)
=ROUNDUP(31415.92654, -3)
SEC Syntax: SEC(number)
Returns the secant of an angle.
=SEC(A1)
=SEC(15)
SECH Syntax: SECH(number)
Returns the hyperbolic secant of an angle.
=SECH(A1)
=SECH(15)
SIGN Syntax: SIGN(number)
Returns 1 if the number is positive, zero (0) if the number is 0, and -1 if the number is negative.
=SIGN(5.5)
=SIGN(A1)
SIN Syntax: SIN(number)
Returns a Double specifying the sine of an angle. Number is a Double or any valid numeric expression that expresses an angle in radians.
=SIN(5.5)
=SIN(A1)
SINH Syntax: SINH(number)
Returns the hyperbolic sine of a number.
=SINH(5.5)
=SINH(A1)
SQRT Syntax: SQRT(number)
Returns a positive square root
=SQRT(a1)
SQRTPI Syntax: SQRTPI(number)
Returns a positive square root of (number * pi).
=SQRTPI(2)
=SQRTPI(A1)
SUBTOTAL Syntax: SUBTOTAL(function_num, ref1, ref2, ...)
Returns a subtotal in a list or database.

Function_num is the number 1 to 11 (includes hidden values) that specifies which function to use in calculating subtotals within a list.
=SUBTOTAL(1,A1:A2)
=SUBTOTAL(2, a1:a5)
SUM Syntax: SUM(number1, number2, ...)
Returns the sum of corresponding array numbers
=SUM(a1,a2)
=SUM(a1:a5)
=SUM(1,3,4,-1,-2,-5,6,7...)
SUMIF Syntax: SUMIF(range, criteria, [sum_range])
Adds all numbers in a range of cells, based on a given criteria. Range is the range of cells that you want to apply the criteria against. Criteria is used to determine which cells to add. Sum_range are the cells to sum.
=SUMIF(A1:A8,">1000",B1:B8)
SUMIFS Syntax: SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Adds the cells in a range that meet multiple criteria.
=SUMIFS(A1:A20, B1:B20, ">0", C1:C20, ">10")
SUMPRODUCT Syntax: SUMPRODUCT(array1, array2, ...)
Returns the sum of the products of corresponding array components.
=SUMPRODUCT(a1:b2, D1:E2)
=SUMPRODUCT(a1,a1,a3)
SUMSQ Syntax: SUMSQ(number1, number2, ...)
Calculates the sum of the squares of numbers.
=SUMSQ(A1:A5)
=SUMSQ(A1, A2,A3...)
=SUMSQ(1,2,3,4...)
SUMXMY2 Syntax: SUMXMY2(array1, array2)
This function calculates the sum of the squares of the differences between values in two arrays and returns the sum of the results.
array1 and array2 are two ranges or arrays.
=SUMXMY2(A1:A8,B1:B8)
SUMX2PY2 Syntax: SUMX2PY2(array1, array2)
This function calculates the sum of the squares of corresponding values in two arrays and returns the sum of the results.
array1 and array2 are two ranges or arrays.
=SUMX2PY2(A1:A8,B1:B8)
SUMX2MY2 Syntax: SUMX2MY2(array1, array2)
This function calculates the difference of squares of corresponding values in two arrays and returns the sum of the results.
array1 and array2 are two ranges or arrays.
=SUMX2MY2(A1:A8,B1:B8)
TAN Syntax: TAN(number)
Returns a Double specifying the tangent of an angle. The required number is a Double or any valid numeric expression that expresses an angle in radians.
=TAN(0.823)
=TAN(A1)
TANH Syntax: TANH(number)
Returns the hyperbolic tangent of a number.
=TANH(0.823)
=TANH(A1)
TRUNC Syntax: TRUNC(number)
Truncates a number to an integer.
=TRUNC(314.1592, 2)
=TRUNC(A1)