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.
