Math Functions

ABS

ABS(number)

Returns the absolute value of a number.

For example:
1. ABS(-12), returns 12.
2. ABS(12), returns 12.

AVERAGE

AVERAGE(number1,number2,...)

Returns the average of its arguments.

For example:
AVERAGE(1,3,5), returns 3.

CEILING

CEILING(number,significance)

Returns number rounded up, away from zero, to the nearest multiple of significance.

There are two arguments in the CEILING function:

number: Required. The value you want to round.
significance: Required. The multiple to which you want to round.

Here are the calculation rules:

If number is positive, regardless of whether significance is positive or negative, the value is rounded up away from 0.
If number is negative and significance is positive, the value is rounded up towards zero.
If both number and significance are negative, the value is rounded down away from zero.

For example:

Formula	Description	Result
CEILING(10,3)	Rounds 10 up to the nearest multiple of 3.	12
CEILING(10,-3)	Rounds 10 up to the nearest multiple of 3.	12
CEILING(-10,3)	Rounds -10 up to the nearest multiple of 3.	-9
CEILING(-10,-3)	Rounds -10 down to the nearest multiple of -3.	-12

COS

COS(radians)

Returns the cosine of the given angle. The return value is between -1 and 1.

When using the COS function, you need to convert the angle from degrees to radians through the RADIANS function.

For example:
COS(RADIANS(60)), returns 0.5.

COT

COT(radians)

Return the cotangent of the given angle.

When using the COT function, you need to convert the angle from degrees to radians through the RADIANS function.

For example:
COT(RADIANS(45)), returns 1.

COUNT

COUNT(value1,value2,...)

Counts how many numbers are in the list of arguments.

For example:
COUNT("Jessica","Jay","Tom","Helen"), returns 4.

COUNTIF

COUNTIF(value1,value2,...,“criteria”)/COUNTIF([value1,value2,...],“criteria”)

Returns the number of values that meet a criterion.

For example:
1. COUNTIF([1,2,3,4],">2"), returns 2.
2. COUNTIF([1,2,3,4],"!=2"), returns 3.
3. COUNTIF([1,2,3,4],">=1"), returns 4.
4. COUNTIF(["a","b","c"],"d"), returns 0.

FLOOR

FLOOR(number,significance)

Rounds number down, toward zero, to the nearest multiple of significance.

There are two arguments in the FLOOR function:

number: Required. The numeric value you want to round.
significance: Required. The multiple to which you want to round.

The usage of the FLOOR function is similar to the CEILING function.

FIXED

FIXED(number,decimals)

Rounds a number to the specified number of decimals, formats the number in decimal format using a period and commas, and returns the result as text.

There are two arguments in the FIXED function:

number: Required. The number you want to round and convert to text.
decimals: Optional. The number of digits to the right of the decimal point.

For example:

Formula	Description (Result)
=FIXED(1234.567, 1)	Rounds the first number 1 digit to the right of the decimal point (1,234.6).
=FIXED(1234.567, -1)	Rounds the first number 1 digit to the left of the decimal point (1,230).
=FIXED(-1234.567, -1, TRUE)	Rounds the second number 1 digit to the left of the decimal point, without commas (-1230).
=FIXED(44.332)	Rounds the third number 2 digits to the right of the decimal point (44.33).

INT

INT(number)

Rounds a number down to the nearest integer.

LARGE

LARGE([array],k)

Returns the k-th largest value in a data set.

There are two arguments in the LARGE function:

array: Required. The array or range of data for which you want to determine the k-th largest value.
k: Required. The position (from the largest) in the array or cell range of data to return.

For example:
LARGE([89,98,76],1), returns 98.

LOG

LOG(number,base)

Returns the logarithm of a number to the base you specify.

There are two arguments in the LOG function:

number: Required. The positive real number for which you want the logarithm.
base: Optional. The base of the logarithm. If base is omitted, it is assumed to be 10.

MOD

MOD(number,divisor)

Returns the remainder after number is divided by divisor. The result has the same sign as divisor.

There are two arguments in the MOD function:

number: Required. The number for which you want to find the remainder.
divisor: Required. The number by which you want to divide number.

MAX

MAX(number1,number2,…)

Returns the largest value in a set of values.

For example:
MAX(1,5,3), returns 5.

MIN

MIN(number1,number2,…)

Returns the minimum value in a set of values.

For example:
MIN(1,5,3), returns 1.

POWER

POWER(number,power)

Returns the result of a number raised to a power.

There are two arguments in the POWER function.

number: Required. The base number. It can be any real number.
power: Required. The exponent to which the base number is raised.

PRODUCT

PRODUCT(number1,number2,…)

Multiplies all the numbers given as arguments and returns the product.

You can also perform the same operation using the multiply (*) mathematical operator.

RADIANS

RADIANS(degrees)

Converts degrees to radians.

For example:
RADIANS(180), returns 3.14159265.

RAND

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 field is calculated.

For example:

Formula	Description
RAND()	A random number greater than or equal to 0 and less than 1.
RAND()*100	A random number greater than or equal to 0 and less than 100.
RAND()*(b-a)+a	A random number greater than or equal to a and less than b.
INT(RAND()*(b-a)+a)	A random whole number greater than or equal to a and less than b.

Scenario Examples:

Ways of Extracting Questions	Description	Formula
One question is randomly selected from the 20th to 30th questions (including the 20th and excluding the 30th questions).	Greater than or equal to 20 but less than 30.	INT(RAND()*(30-20)+20)
One question is randomly selected from the 20th to 30th questions (including the 20th and 30th questions).	Greater than or equal to 20 but less than 31.	INT(RAND()*(31-20)+20)
One question is randomly selected from the 20th to 30th questions (excluding the 20th question and including the 30th question).	Greater than or equal to 21 but less than 31.	INT(RAND()*(31-21)+21)
One question is randomly selected from the 20th to 30th questions (excluding the 20th question and the 30th question).	Greater than or equal to 21 but less than 30.	INT(RAND()*(30-21)+21)

ROUND

ROUND(number,num_digits)

Rounds a number to a specified number of digits.

There are two arguments in the ROUND function:

number: Required. The number that you want to round.
num_digits: Required. The number of digits to which you want to round the number argument.

For example:
ROUND(88/3,2), returns 29.33.

SIN

SIN(radians)

Returns the sine of the given angle.

When using the SIN function, you need to convert the angle from degrees to radians through the RADIANS function.

For example:
SIN(RADIANS(30)), returns 0.5.

SMALL

SMALL([array],k)

Returns the k-th minimum value in a data set. The syntax is similar to the LARGE function.

There are two arguments in the SMALL function:

array: Required. An array or range of numerical data for which you want to determine the k-th minimum value.
k: Required. The position (from the smallest) in the array or range of data to return.

For example:
SMALL([89,98,76],1), returns 76.

SQRT

SQRT(number)

Returns a positive square root of the number given.

If number is negative, SQRT returns 0.

SUM

SUM(number1,[number2],…)

Adds values given as arguments and returns the sum.

SUMIF

SUMIF(range,“criteria”,sum_range)

Returns the sum of the field values in a subform that meets the specified criteria.

There are three arguments in the SUMIF function:

range: Required. The field that you want evaluated by criteria. Supported fields include: Number in subforms, Single Line, Single Select, and Radio.
criteria: Required. It defines which fields will be added. Supported forms and using rules are in the following table:

Supported Form	Whether Double Quotation Marks Are Required	Example	Note
Number	Not required	20, 32	/
Expression	Required	">32", "!=apple"	Supported operators include: >, <, ==, !=, >=, <=
Text	Required	"Apples", "Fruits"	/
Field	Not required	Field	1. When using the SUMIF function in a form field, only form fields can be selected. 2. When using the SUMIF function in a subform field, only form fields and current subform fields can be selected.

sum_range: Optional. The Number fields containing the values to add. If sum_range is omitted, range is calculated automatically. Supported fields include: Number in subforms, Single Line, Single Select, and Radio.

For example, to sum the inbound quantity of the fruit category in the Inbound Details form, you can set the formula in the Total Amount (Fruit) field as follows:

SUMIF(Inbound Details.Product Category,"fruit",Inbound Details.Quantity)

SUMIFS

SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,...)

Returns the sum of the field values in a subform that meets multiple criteria that you specify.

There are four kinds of arguments in the SUMIFS function:

sum_range: Required. The Number fields containing the values to add. Supported fields include: Number in subforms, Single Line, Single Select, and Radio.
criteria_range1: Required. The field that is tested using criteria1. criteria_range1 and criteria1 set up a search pair whereby a field is searched for specific criteria. Once the field is found, their corresponding values in sum_range are added. Supported fields include: Number in subforms, Single Line, Single Select, and Radio.
criteria1: Required. The criteria that defines which fields in Criteria_range1 will be added. Supported forms and using rules are in the following table:

Supported Form	Whether Double Quotation Marks Are Required	Example	Note
Number	Not required	20, 32	/
Expression	Required	">32", "!=apple"	Supported operators include: >, <, ==, !=, >=, <=
Text	Required	"Apples", "Fruits"	/
Field	Not required	Field	1. When using the SUMIFS function in a form field, only form fields can be selected. 2. When using the SUMIFS function in a subform field, only form fields and current subform fields can be selected.

criteria_range2, criteria2,...: Optional. The rules are the same as above.

Note:
1. All criteria_range arguments must come from the same subform as sum_range arguments, otherwise the calculation will fail. criteria_range arguments can select the same field as sum_range arguments.
2. Up to 127 conditions can be entered, meaning the function requires a minimum of 3 arguments and a maximum of 255 arguments.

For example, to sum the inbound quantity of the apple type in the fruit category in the inbound Details form, you can set the formula in the Total Amount (Apple) field as follows:

SUMIFS(Inbound Details.Quantity,Inbound Details.fruit,"apple",Inbound Details.Product Name,"apple")

SUMPRODUCT

SUMPRODUCT([array1],[array2])

Returns the sum of the products of corresponding ranges or arrays. The default operation is multiplication.

array: Optional. Multiple numeric arrays.

It can be mainly used to calculate weighted sums. For example, SUMPRODUCT([1,2,3],[0.1,0.2,0.3]) is equal to 1 * 0.1 + 2 * 0.2 + 3 * 0.3 = 1.4.

TAN

TAN(radians)

Returns the tangent of the given angle.

When using the TAN function, you need to convert the angle from degrees to radians through the RADIANS function.

For example:
TAN(RADIANS(45)), returns 1.