Numeric Functions
A numeric operation function is a type of function in a formula that can operate on data of a numeric type.
SUM()
Add all values together.
SUM(number1, number2,... )
Parameter Description
Number: indicates the numerical parameter for the operation.Fields of the numeric type include numbers, currencies, percentages, scores, and so on.
Example
// Add only numeric values
Formula: SUM(1, 2, 3)
Result: 6
// Add the numeric value to the text value
Formula: SUM(1, 2, "3")
Result: 6
// Add numeric values to text
SUM(1, 2, "3 ")
Result: 3
// Add numeric fields
Formula: SUM({math score}, {English score}, {Chinese score})
Result: Number
// Add a field of numeric type to a numeric value
Formula: SUM({math score}, {English score}, 60)
Result: Number
AVERAGE()
Calculate the arithmetic mean of multiple values.
AVERAGE(number1, number2,... )
Parameter Description
Number: indicates the numerical parameter for the operation.Fields of the numeric type include numbers, currencies, percentages, scores, and so on.
Example
// Average a pure value
Formula: AVERAGE(1, 2, 3)
Result: 2
// Average the value with the text value
Formula: AVERAGE(1, 2, "3")
Result: 2
// Average the value with the text
Formula: AVERAGE(1, 2, "3 ")
Result: 1
// Find the average value of a numeric field
Formula: AVERAGE({math score}, {English score}, {Chinese score})
Result: Number
// Average the fields of the numeric type with the numeric value
Formula: AVERAGE({math score}, {English score}, 60)
Result: Number
MAX()
Returns the largest of multiple values.
MAX(number1, [number2, …])
Parameter Description
Number: indicates the numerical parameter for the operation.Fields of the numeric type include numbers, currencies, percentages, scores, and so on.
In this case, if the input values of this function are in date format, the latest date can be compared among multiple dates.
Example
// Find the maximum value of a pure value
Formula: MAX(1, 2, 3)
Result: 3
// Maximizes the value with the text value
Formula: MAX(1, 2, "3")
Result: 3
// Maximizes the value with the text
Formula: MAX(1, 2, "3 ")
Result: 2
// Maximizes the value of a field of a numeric type
Formula: MAX({Math score}, {English score}, {Chinese score})
Result: Number
// Maximizes the value of a field of a numeric type
Formula: MAX({Math score}, {English score}, 60)
Result: Number
// Find the latest date for a field of date type
Formula: MAX({time 1}, {time 2})
Result: date
MIN()
Returns the smallest of multiple values.
MIN(number1, [number2, …])
Parameter Description
Number: indicates the numerical parameter for the operation.Fields of the numeric type include numbers, currencies, percentages, scores, and so on.
In this case, if the input values of this function are all in date format, the earliest date of multiple dates can be compared.
Example
// Find a pure numerical minimum
Formula: MIN(1, 2, 3)
Result: 1
// Minimize numeric values with text values
Formula: MIN("1", 2, 3)
Result: 1
// Minimize the value with the text
Formula: MIN(" one ", 2, 3)
Result: 2
// Minimize the value of a numeric field
Formula: MIN({math score}, {English score}, {Chinese score})
Result: Number
// Find the minimum value for a field of a numeric type
Formula: MIN({math score}, {English score}, 60)
Result: Number
// Find the latest date for a field of date type
Formula: MIN({time 1}, {time 2})
Result: date
ROUND()
Rounds a value to the specified number of digits.
ROUND(value, [precision]);
Parameter Description
Value: indicates the rounded value. Precision: Optional, specifies the number of rounded digits.If this parameter is not entered, the default value is 0.
If precision > 0, round to the specified decimal place; If precision = 0, round to the nearest integer; If precision < 0, round to the left of the decimal point.
Example
// If precision is not specified, it defaults to 0.
Formula: ROUND(1.55);
Result: 2.0;
// precision > 0 to round to the specified decimal place.
Formula: ROUND(1.45, 1);
Result: 1.5;
// precision = 0, rounded to the nearest integer.
Formula: ROUND(1.45, 0);
Result: 1.0;
// precision < 0, rounded to the left of the decimal point.
Formula: ROUND(321.45, -2);
Result: 300.0;
// precision > 0. If the value is a decimal, the system automatically rounded down the precision (1.6 >> 1.0).
Formula: ROUND(5.45, 1.6);
Result: 5.5;
ROUNDUP()
Rounds the value in the direction of increasing the absolute value.
ROUNDUP(value, [precision]);
Parameter Description
Value: indicates the value to be rounded. Precision: Optional, specifying the number of digits to round off the value.If this parameter is not entered, the default value is 0. Direction of absolute value increase: that is, away from 0 direction.
'ROUNDUP()' is similar to 'ROUND()' except that it always rounds the value in the direction of increasing the absolute value. If precision > 0, round to the specified decimal place; If precision = 0, round to the nearest integer; If precision < 0, rounding is done to the left of the decimal point.
Example
// If precision is not specified, it defaults to 0.
Formula: ROUNDUP(1.55);
Result: 2.0;
Formula: ROUNDUP(1.11);
Result: 2.0;
// precision > 0, rounding to the specified decimal place.
Formula: ROUNDUP(1.11, 1);
Result: 1.2;
// precision = 0, rounded to the nearest integer.
Formula: ROUNDUP(1.11, 0);
Result: 2.0;
// precision < 0, rounding to the left of the decimal point.
Formula: ROUNDUP(321.45, -2);
Result: 400.0;
// precision > 0. If the value is a decimal, the system automatically rounded down the precision (1.6 >> 1.0).
Formula: ROUNDUP(5.45, 1.6);
Result: 5.5;
ROUNDDOWN()
Rounds the value in the direction of decreasing the absolute value.
ROUNDDOWN(value, [precision]);
Parameter Description
Value: indicates the value to be rounded. Precision: Optional, specifying the number of digits to round off the value.If this parameter is not entered, the default value is 0. direction of absolute value decrease: that is, near 0 direction.
'ROUNDDOWN()' is similar to 'ROUND()' except that it always rounds the value in the direction of decreasing the absolute value. If precision > 0, round to the specified decimal place; if precision = 0, round to the nearest integer; If precision < 0, rounding is done to the left of the decimal point.
Example
// If precision is not specified, it defaults to 0.
Formula: ROUNDDOWN(1.55);
Result: 1.0;
Formula: ROUNDDOWN(1.11);
Result: 1.0;
// precision > 0 to round to the specified decimal place.
Formula: ROUNDDOWN(1.11, 1);
Result: 1.1;
// precision = 0, rounded to the nearest integer.
Formula: ROUNDDOWN(1.11, 0);
Result: 1.0;
// precision < 0, rounded to the left of the decimal point.
Formula: ROUNDDOWN(321.45, -2);
Result: 300.0;
// precision > 0. If the value is a decimal, the system automatically rounded down the precision (1.6 >> 1.0).
Formula: ROUNDDOWN(5.45, 1.6);
Result: 5.4;
CEILING()
Rounds a value up to a multiple of the nearest specified cardinality.
CEILING(value, [significance]);
Parameter Description
Value: indicates the value to be rounded. Significance: is not required, is the cardinality used to round up, the return value is a multiple of the cardinality.If this parameter is not entered, the default value is 1. Round up: that is, the return value must be greater than or equal to the original value.
If value > 0, significance > 0, the value is rounded in the direction away from 0. If value < 0, significance > 0, the value is rounded in the direction toward 0. If significance < 0, the error value NaN is returned.
Example
// If the value is not specified, significance defaults to 1.
Formula: between(1.55);
Result: 2.0;
Formula: between(1.11);
Result: 2.0;
// If value > 0, significance > 0, the value is rounded in the direction away from 0.
Formula: CEILING(1.11, 1);
Result: 2.0;
Formula: CEILING(1.11, 0.1);
Result: 1.2;
// If value < 0, significance > 0, the value is rounded in the direction toward 0.
Formula: CEILING(-1.11, 1);
Result: -1.0;
Formula: CEILING(-1.99, 0.1);
Result: -1.9;
// If significance < 0, the error value NaN is returned.
Formula: CEILING(1.11, -1);
Result: NaN;
FLOOR()
Rounds the value down to a multiple of the nearest specified cardinality.
FLOOR(value, [significance]);
Parameter Description
Value: indicates the value to be rounded. Significance: is not required, is the cardinality used to round down, the return value is a multiple of the cardinality.If this parameter is not entered, the default value is 1. Round down: that is, the return value must be less than or equal to the original value.
If value > 0, significance > 0, the value is rounded in the direction toward 0. If value < 0, significance > 0, the value is rounded in the direction away from 0. If significance < 0, the error value NaN is returned.
Example
// If the value is not specified, significance defaults to 1.
Formula: FLOOR(1.55);
Result: 1.0;
Formula: FLOOR(1.11);
Result: 1.0;
// If value > 0, significance > 0, the value is rounded in the direction toward 0.
Formula: FLOOR(1.55, 1);
Result: 1.0;
Formula: FLOOR(1.55, 0.1);
Result: 1.5;
// If value < 0, significance > 0, the value is rounded in the direction away from 0.
Formula: FLOOR(-1.11, 1);
Result: -2.0;
Formula: FLOOR(-1.99, 0.1);
Result: -2.0;
// If significance < 0, the error value NaN is returned.
Formula: FLOOR(1.11, -1);
Result: NaN;
EVEN()
Rounds the value to the nearest even number in the direction of increasing the absolute value.
EVEN(value);
Parameter Description
Value: indicates the value to be rounded. Direction of absolute value increase: that is, away from 0 direction.
Example
// value > 0
Formula: EVEN(1.5);
Result: 2.0;
Formula: EVEN(3);
Result: 4.0;
//value = 0
Formula: EVEN(0);
Result: 0.0;
// value < 0
Formula: EVEN(1.5);
Result: -2.0;
Formula: EVEN(3);
Result: -4.0;
ODD()
Rounds the value to the nearest odd number in the direction of increasing the absolute value.
ODD(value);
Parameter Description
Value: indicates the value to be rounded. Direction of absolute value increase: that is, away from 0 direction.
Example
// value > 0
Formula: ODD(1.5);
Result: 3.0;
Formula: ODD(3);
Result: 3.0;
//value = 0
Formula: ODD(0);
Result: 1.0;
// value < 0
Formula: ODD(1.5);
Result: -3.0;
Formula: ODD(3);
Result: -3.0;
INT()
Rounds the value down to the nearest integer.
INT(value);
Parameter Description
Value: indicates the value to be rounded. Round down: that is, the return value must be less than or equal to the original value.
Example
// value > 0
Formula: INT(1.5);
Result: 1.0;
Formula: INT(3);
Result: 3.0;
//value = 0
Formula: INT(0);
Result: 0.0;
// value < 0
Formula: INT(1.5);
Result: -2.0;
Formula: INT(3);
Result: -3.0;
ABS()
Takes the absolute value of the value.
ABS(value);
Parameter Description
Value: indicates the value to be evaluated in absolute value. Absolute value: The absolute value of a positive number is itself, and the absolute value of a negative number is the negative sign removed.
Example
// value > 0
Formula: ABS(1.5);
Result: 1.5;
Formula: ABS(3);
Result: 3.0;
//value = 0
Formula: ABS(0);
Result: 0.0;
// value < 0
Formula: ABS(1.5);
Result: 1.5;
Formula: ABS(3);
Result: 3.0;
SQRT()
Computes the arithmetic square root of a value.
SQRT(value);
Parameter Description
Value: indicates the value to take the square root of.The argument value >= 0 is required, otherwise the function returns NaN. Arithmetic square root: The square root of 4 is ± 2. 2 is the arithmetic square root.
Example
// value > 0
Formula: SQRT(4);
Result: 2.0;
//value = 0
Formula: SQRT(0);
Result: 0.0;
// value < 0
Formula: SQRT(1.5);
Result: NaN;
MOD()
Divide two values to take the remainder.
MOD(value, divisor);
Parameter Description
Value: indicates the dividend. Divisor: is the divisor. Remainder: the part of integer division where the dividend is not completely divided.
The return symbol is the same as that of divisor.
Example
// Return the remainder if the value is not completely divided.
Formula: MOD(7, 3);
Result: 1.0;
Formula: MOD(2, 3);
Result: 2.0;
// If the value is not fully divisible, the symbol of the result returned is the same as that of the divisor.
Formula: MOD(7, 3);
Result: 1.0;
Formula: MOD(7, -3);
Result: -1.0;
Formula: MOD(-7, -3);
Result: -1.0;
// When the value is completely divided, return 0
Formula: MOD(6, 3);
Result: 0.0;
POWER()
Computes the power of a value(base).
POWER(base, power);
Parameter Description
Base: indicates the base. Power: indicates the index.
Power > 1, return e to the power; Power < 1 and power > 0, return e to the square root of 1/power; Power = 0, returns 1; Power < 0, return e inverse to the power power.
Example
// Power > 1, return base power power;
Formula: POWER(2, 3)
Result: 8.00
// Power < 1 and power > 0, return base root 1/power;
Formula: POWER(8, 1/3)
Result: 2.00 (8 to the square root of 3)
// If power = 0, 1 is returned.
Formula: POWER(2, 0)
Result: 1.00
// If power < 0, return the power power of base inverse.
Formula: POWER(2, -2)
Result: 0.25 (1/2 to the second power)
// If base < 0 and power is decimal, NaN is returned.
Formula: POWER(-8, 1/3)
Result: NaN
EXP()
Compute the power of e.
EXP(power);
Parameter Description
Power: indicates the index. e: The natural number e is about 2.718282, which is the base of the natural logarithm.
Power > 1, return base power power; Power < 1 and power > 0, return base root 1/power; Power = 0, returns 1; Power < 0, return base inverse power power; If base < 0 and power is decimal, NaN is returned.
Example
// Power > 1, return e to the power;
Formula: EXP (2)
Result: 7.39
// Power < 1 and power > 0, return e to the square root of 1/power;
Formula: EXP (1/2)
Result: 1.65 (e to the square root of 2)
// If power = 0, 1 is returned.
Formula: EXP (0)
Result: 1.00
// If power < 0, return e inverse to the power.
Formula: EXP (2)
Result: 0.14 (1/e to the second power)
LOG()
Computes the logarithm of a value based on the specified base.
LOG(number, [base]);
Parameter Description
Number: The number used to calculate the logarithm. Number must be > 0, otherwise the function outputs NaN. Base: Optional, is the base of logarithms (base > 0 and base ≠ 1).It operates as the base of the logarithm.If no cardinality is specified, the default is 10. Logarithm: logarithm is the inverse of exponentiation.
Example
// number > 0, do not enter base;
Formula: LOG (100). Result: 2.00
// number > 0, base > 0 and base ≠ 1;
Formula: LOG(8, 2)
Result: 3
Formula: LOG(1/8, 1/2)
Result: 3
// number <= 0;
Formula: LOG (100)
Result: NaN
// number > 0, base < 0 or base = 1;
Formula: LOG(8, -2)
Result: NaN
Formula: LOG(8, 1)
Result: NaN
VALUE()
Converts a text value to a numeric value.
VALUE(text);
Parameter Description
Text: is the text value to be converted.
Example
// Format the input text value.
Formula: the VALUE (" $10 ")
Result: 10.00
Formula: VALUE(" Ticket: ￥10 ")
Result: 10.00
Formula: VALUE(" 125,000 ")
Result: 125.00