Support > Repository > Function > Numerical function
ja | en

Here are 47 functions related to mathematical operations.

ABS |  ACOS |  ADR7.4 |  ATAN |  ATAN2 |  ASIN |  CBRT |  CEILING |  COS |  COSH |  DEGREES |  DIR7.4 |  EVEN |  EXP |  FACT |  FIXED |  FLOOR |  HYPOT |  IEEEremainder |  INT |  LN |  LOG |  LOG1P |  LOG10 |  MOD |  MROUND |  MUR7.4 |  ODD |  PI |  POWER |  QUOTIENT |  RADIANS |  RAND |  RANDBETWEEN |  RINT |  ROUND |  ROUNDDONW |  ROUNDUP |  SIN |  SINH |  SIGN |  SIGNUM |  SQRT |  SUR7.4 |  TAN |  TANH |  TRUNC

ABS (number)

Returns the absolute value of the numeric value of the argument.The return value type is "integer".

Example:

ABS(${item1})

An example Behavior
ABS(10) 10
ABS(-10) 10
ABS(-3.14) 3.14

ACOS (value of cosine of desired angle)

Returns the arc cosine of a number.The arc cosine is the angle that its cosine is a number.

Example:

ACOS(${item1})

For input value item 1, specify the cosine value of the desired angle in the range of -1 to 1. The return angle is in radians ranging from 0 (zero) to PI.

ADD (numerical value, numerical value)

ADD (numeric string representation, numeric string representation)

Add the number of arguments.Internally, we use BigDecimal class provided by Java to perform operations.It is suitable for strict calculation including floating point numbers.The return value is a string representation of the numeric value.

Example:

VALUE(ADD("0.001", "123.456"))
VALUE(ADD(0.001, 123.456))
The two above are not strictly the same.Already contains an error when expressing a floating point number of 0.001.For more reliable operation, it is better to use a character string as an argument, such as "0.001" for the floating point part.
VALUE(ADD(1000, "0.1"))

In this way, you can mix numbers and string expressions in arguments.

To the return value of the ADD functionVALUEBy applying the function, you can convert it to an 8-byte floating point number.

ATAN (tangent value of the desired angle)

Returns the arc tangent of a number.An arc tangent is an angle such that its tangent is a numerical value.

Example:

ATAN(${item1})

For input value item 1, specify the tangent value of the angle to be obtained.The return angle is in the range of -PI/2 to PI/2 radians.

ATAN 2 (x coordinate value, y coordinate value)

Returns the arc tangent of the specified xy coordinate. Arc tangent is the angle from the x axis to the straight line connecting the origin 0 and the point represented by the x and y coordinates.

Example:

ATAN2(${item1}, ${item2})

For the input values ​​item1 and item2, specify the x coordinate of the x point and the y coordinate of the y point, respectively. The return angle is in the range of -PI to PI (except -PI) radians.

ASIN (the value of the sign of the desired angle)

Returns the arc sine of a number.An arc sine is an angle such that its sign is numeric.

Example:

ASIN(${item1})

The input value item 1 specifies the value of the sine of the desired angle in the range of -1 to 1.The return angle is in the range of -PI/2 to PI/2 radians.

CBRT (number)

Returns the cube root of the numeric value of the argument.

Example:

CBRT(${item1})

CEILING (numerical value, reference value)

Rounds a number close to a multiple of the reference value.The return type is 8 byte floating point type.

Example:

CEILING(${item1}, 1)

An example Behavior
CEILING(2.5, 1) 3
CEILING(-2.5, -2) -4
CEILING(-2.5, 2) -2.5
CEILING(1.5, 0.1) 1.5

It can be applied to time.

An example Behavior
TEXT(CEILING(${time}, "1:00"), "hh:mm") "12:00"
* When the type of $ {time} item is time type and value is "11: 30: 00"
DATETIMEVALUE(TEXT(DATE(${datetime}) + CEILING(TOTIME(${datetime}), "1:00"), "yyyy-MM-dd HH:mm:ss")) 2008-3-14 12:00:00
* When the type of the $ {datetime} item is a date-time type and the value is "2008-3-14 11: 30: 00"

COS (number)

Returns the cosine of the specified angle.Specify the angle for which cosine is to be measured in units of radians.The return type is 8 byte floating point type.

Example:

COS(${item1})

An example Behavior
COS(60d*PI()/180d) 0.5
※ In the case of real number calculation, please give "d" after the number.

COSH (real number to find hyperbolic cosine)

Returns the hyperbolic cosine of a number (hyperbolic cosine).

Example:

COSH(${item1})

DEGREES (number)

Convert radians to degrees.The return type is 8 byte floating point type.

Example:

DEGREES(${item1})

An example Behavior
DEGREES(PI()) 180

DIV (numerical value, numerical value, scale [, rounding mode])

DIV (numeric string representation, numeric string representation, scale [, rounding mode])

Gradually count the number of arguments.Internally, we use BigDecimal class provided by Java to perform operations.It is suitable for strict calculation including floating point numbers.The return value is a string representation of the numeric value.

Example:

VALUE(DIV(ADD(MUL(10000, "0.12"),MUL(10000, "0.34")),100,2,"HALFUP"))

As in the example above, you can mix numbers and string expressions in arguments.Since errors are already included at the stage of expressing a floating point number of 0.12, it is better to use a character string as an argument, such as "0.12" for floating point part when performing more reliable operation.

Scale is the number of digits after the decimal point.

The following notation can be specified for the rounding mode.When omitted, it becomes HALFUP (rounding off).

Designation Description
CEILING Rounding mode to approach positive infinity
DOWN Rounding mode to approach zero
FLOOR Rounding mode to approach negative infinity
HALFDOWN Rounding
HALFEVEN Round to end with even number
HALFUP * When omitted Rounding off
UP Rounding mode to round off from 0

To the return value of the DIV functionVALUEBy applying the function, you can convert it to an 8-byte floating point number.

EVEN (number)

Returns the value rounded up to the nearest even number.The return value type is integer type.

Example:

EVEN(${item1})

An example Behavior
EVEN(1.5) 2
EVEN(3) 4
EVEN(-1) -2
EVEN(0) 0

EXP (number)

Returns the power of a number whose base is e.The return type is 8 byte floating point type.

Example:

EXP(${item1})

FACT (number)

Returns the factorial of a number.If a numeric value other than an integer is specified, decimal places are truncated.The return value type is an 8-byte integer.

Example:

FACT(${item1})

An example Behavior
FACT(5) 120

FIXED (number, number of digits, thousands separator)

Rounded the number and is composed of a period (".") And a comma (",")Convert to stringTo do. The number of digits indicates the rounding position.If omitted, it is regarded as the second decimal place. Specify "true" or "false" for thousands separator.If omitted, it is assumed to be false.

Example: Round off at the third digit and perform digit separation (insert "," for every 3 digits).

FIXED(${item1}, 3, true)

An example Behavior
FIXED(123.4, 0, false) "123"
FIXED(1234567, -1, true) "1,234,570"
FIXED(1234.567, 5) "1234.56700"
FIXED(1234.567) "1234.57"

FLOOR (numerical value, reference value)

Round the number down to a value that is a multiple of the reference value.The return type is 8 byte floating point type.

Example:

FLOOR(${item1}, 1)

An example Behavior
FLOOR(2.5, 1) 2
FLOOR(-2.5, -2) -2
FLOOR(-2.5, 2) -2.5
FLOOR(1.5, 0.1) 1.5

It can be applied to time.

An example Behavior
TEXT(FLOOR(${time}, "1:00"), "hh:mm") "11:00"
※ When the type of $ {time} item is time type and value is "11: 30: 00"
DATETIMEVALUE(TEXT(DATE(${datetime}) + FLOOR(TOTIME(${datetime}), "1:00"), "yyyy-MM-dd HH:mm:ss")) 2008-3-14 11:00:00
* When the type of the $ {datetime} item is a date time type and the value is "2008-3-14 11: 30: 00"

HYPOT (numerical value 1, numerical value 2)

With no intermediate overflow or underflow sqrt (x2+y2) Will be returned.

Example:

HYPOT(${x}, ${y})

IEEEremainder (numerical value 1, numerical value 2)

Compute the remainder for two arguments according to the IEEE 754 standard.

Example:

IEEEremainder(${item1}, ${item2})

INT (number)

Returns the largest integer not exceeding the specified number.

Example:

INT(${item1})

An example Behavior
INT(8.9) 8
INT(-8.1) -9

LN (number)

Returns the natural logarithm of a number.The return type is 8 byte floating point type.

Example:

LN(${item1})

LOG (number)

Returns the natural logarithm of a number.The return type is 8 byte floating point type.

Example:

LOG(${item1})

LOG (number, bottom)

Returns the logarithm of a number based on the specified number.The return type is 8 byte floating point type.

Example:

LOG(${item1}, ${item2})

LOG 1 P (numerical value)

Returns the natural logarithm of the sum of the argument and 1.

Example:

LOG1P(${num})

LOG 10 (numerical value)

Returns the logarithm (common logarithm) of the numerical value with base 10.The return type is 8 byte floating point type.

Example:

LOG10(${item1})

An example Behavior
LOG(10) 1.0
LOG(8,2) 3.0
LOG10(10) 1.0

MOD (number, divisor)

Find the remainder by dividing the number by the divisor.The return value type is integer type.

Example:

MOD(${item1}, ${item2})

An example Behavior
MOD(10,3) 1

MROUND (numerical value, reference value)

Rounds a number to a multiple of the reference value (rounded off).The return type is 8 byte floating point type.

Example:

MROUND(${item1}, 1)

An example Behavior
MROUND(10, 3) 9
MROUND(-10, -3) -9
MROUND(1.3, 0.2) 1.4
MROUND(5, -2) 5

It can be applied to time.

An example Behavior
TEXT(MROUND(${time}, "0:30"), "hh:mm") "11:30"
※ When the type of $ {time} item is time type and value is "11: 31: 00"
DATETIMEVALUE(TEXT(DATE(${datetime}) + MROUND(TOTIME(${datetime}), "0:30"), "yyyy-MM-dd HH:mm:ss")) 2008-3-14 11:30:00
* When the type of the $ {datetime} item is a date-time type and the value is "2008-3-14 11:31:00"

MUL (numerical value, numerical value)

MUL (numeric string representation, numeric string representation)

Multiplies the number of arguments.Internally, we use BigDecimal class provided by Java to perform operations.It is suitable for strict calculation including floating point numbers.The return value is a string representation of the numeric value.

Example:

VALUE(MUL("123.456", "456.789"))
VALUE(MUL(123.456, 456.789))
The two above are not strictly the same.When expressing a floating point number of 123.456, errors are already included.For more reliable operation, it is better to use a character string as an argument, such as "123.456" for the floating point part.
VALUE(MUL(1000, "0.1"))

In this way, you can mix numbers and string expressions in arguments.

As the return value of the MUL functionVALUEBy applying the function, you can convert it to an 8-byte floating point number.

ODD (number)

Returns the value rounded up to the nearest odd number.The return value type is integer type.

Example:

ODD(${item1})

An example Behavior
ODD(3) 3
ODD(2) 3
ODD(1.5) 3
ODD(0) 1
ODD(-2) -3

PI()

Returns the numerical value 3.14159265358979 which is an approximate value of pi.The precision of this number is 15 digits.The return type is 8 byte floating point type.

Example:

PI()

POWER (numerical value, exponential)

Returns the exponential power of a number.Numbers and exponents can be integer or decimal.The return type is 8 byte floating point type.

Example:

POWER(${item1}, 2)

An example Behavior
POWER(5,2) 25.0

QUOTIENT (numerator, denominator)

Returns the integer part of the quotient of division.The return value type is integer type.

Example:

QUOTIENT(${item1}, ${item2})

An example Behavior
QUOTIENT(5, 2) 2
QUOTIENT(4.5, 3.1) 1
QUOTIENT(-10, 3) -3

RADIANS (number)

Convert degrees to radians.The return type is 8 byte floating point type.

Example:

RADIAN(${item1})

An example Behavior
RADIANS(270) 4.712389

RAND()

Generate a random number greater than 0 but less than 1.The return type is 8 byte floating point type.

If RAND () is applied to an item A and another item B refers to the value of A, the value of B and the value of A are the same.
When RAND () is used more than once in a certain item A, the result of the first RAND () and the result of the second and subsequent RAND () have the same value.

Example:

RAND()

RANDBETWEEN (minimum value, maximum value)

Returns an integer random number uniformly distributed within the specified range.The return value type is integer type.

If RAND () is applied to an item A and another item B refers to the value of A, the value of B and the value of A are the same.
When RAND () is used more than once in a certain item A, the result of the first RAND () and the result of the second and subsequent RAND () have the same value.

Example:

RANDBETWEEN(${min}, ${max})

RINT (number)

Returns a double value closest to the value of the argument and equal to the computed integer.

Example:

RINT(${item1})

ROUND (number, number of digits)

Rounds a number to the specified number of digits.The type of the return value is "4 byte floating point" or "8 byte floating point".(Depending on the numeric type of the argument, the return type is automatically determined.)

  • If you specify a positive number for the number of digits, the number is rounded off to the right of the decimal point (decimal point), and the number of digits after the decimal point is equal to the number of digits.
  • If 0 is specified for the number of digits, the number is rounded off as the nearest integer.
  • If a negative number is specified for the number of digits, the number will be rounded to the specified digit (the order of 1 is 0) on the left side (integer part) of the decimal point.

Example: When rounding to two decimal places

ROUND(${item1}/${item2}, 2)

An example Behavior
ROUND(2.15, 1) 2.15 will be rounded to the first decimal place (2.2)
ROUND(2.149, 1) 2.149 is rounded to the first decimal place (2.1)
ROUND(-1.475, 2) -1.475 will be rounded to the second decimal place (-1.48)
ROUND(21.5, -1) Rounds 21.5 to the 1st place of the left one digit of the decimal point (20)

This function does not eliminate calculation error.For details about ROUND and calculation precision, see "Formula> basic way of writing>Calculation precisionPlease read.

ROUNDDOWN (number, number of digits)

Truncates a number to the specified number of digits.The type of the return value is "4 byte floating point" or "8 byte floating point".(Depending on the numeric type of the argument, the return type is automatically determined.)

Example: When truncating with two decimal places

ROUNDDOWN(${item1}/${item2}, 2)

This function does not eliminate calculation error.For details about ROUND and calculation precision, see "Formula> basic way of writing>Calculation precisionPlease read.

ROUNDUP (number, number of digits)

Round the number up to the specified number of digits.The type of the return value is "4 byte floating point" or "8 byte floating point".(Depending on the numeric type of the argument, the return type is automatically determined.)

Example: Rounded up to two decimal places

ROUNDUP(${item1}/${item2}, 2)

This function does not eliminate calculation error.For details about ROUND and calculation precision, see "Formula> basic way of writing>Calculation precisionPlease read.

SIN (number)

Returns the sine of the specified angle.Specify the angle for which you want the sign as a unit of radians.The return type is 8 byte floating point type.

Example:

SIN(${item1})

An example Behavior
SIN(PI()/2) 1
SIN(30d*PI()/180d) 0.5
※ In the case of real number calculation, please give "d" after the number.

SINH (real number to find hyperbolic sine)

Returns the hyperbolic sine of a number (hyperbolic sine).

Example:

SINH(${item1})

SIGN (number)

Determines whether the numerical value of the argument is positive/negative/zero. Returns 1 if the number is a positive number, 0 if it is zero, -1 if the number is negative.The return value type is "integer".

Example:

SIGN(${item1})

An example Behavior
SIGN(100) 1
SIGN(0) 0
SIGN(-100) -1

SIGNUM (number)

Returns the sign element of the argument.

Example:

SIGNUM(${item1})

SQRT (number)

Returns the positive square root.If NaN is specified as a numerical value, NaN (special notation meaning invalid operation result) is returned.The return type is 8 byte floating point type.

Example:

SQRT(${item1})

An example Behavior
SQRT(16) 4.0
SQRT(-2) Double.NaN

SUB (numerical value, numerical value)

SUB (numeric string representation, numeric string representation)

Subtract the number of arguments.Internally, we use BigDecimal class provided by Java to perform operations.It is suitable for strict calculation including floating point numbers.The return value is a string representation of the numeric value.

Example:

VALUE(SUB("123.456", "0.001"))
VALUE(SUB(123.456, 0.001))
The two above are not strictly the same.Already contains an error when expressing a floating point number of 0.001.For more reliable operation, it is better to use a character string as an argument, such as "0.001" for the floating point part.
VALUE(SUB(1000, "0.1"))

In this way, you can mix numbers and string expressions in arguments.

To the return value of the SUB functionVALUEBy applying the function, you can convert it to an 8-byte floating point number.

TAN (number)

Returns the tangent of the specified angle.Specify the angle to obtain the tangent in radians.The return type is 8 byte floating point type.

Example:

TAN(${item1})

An example Behavior
TAN(45d*PI()/180d) 1
※ In the case of real number calculation, please give "d" after the number.

TANH (real number to find hyperbolic cosine)

Returns the hyperbolic tangent of a number (hyperbolic tangent).

Example:

TANH(${item1})

TRUNC (number)

Truncates the decimal part of the number and converts it to an integer.The return value type is integer type.

Example:

TRUNC(${item1})

An example Behavior
TRUNC(8.9) 8
TRUNC(-8.1) -8