<< Click to Display Table of Contents >> Navigation: Collect > Collect Enterprise > Template Designer Page > Formula Builder > Functions > Math Functions |
The Math functions are able to compute integer and decimal data and will implicitly convert values to these types when used. Within EQuIS Collect, the functions perform mathematical calculations as data are entered in forms on the Mobile app. Within EQuIS Link, the functions provide a means to transform data when loading to a selected format.
Below is a descriptive list of each math function and its operations. The examples are applicable to either fields in a Collect form or an EQuIS Link data source, unless otherwise noted.
The ABS function calculates the absolute value of a specific parameter. If the parameter cannot be converted to a numeric value or if it is NULL, then the function returns NULL.
ABS(<NumericParameter_1>)
Aggregate = No
Example 1
FieldA |
---|
-4 |
ABS([FieldA]) = 4
This formula is calculating the absolute value of FieldA.
Example 2
FieldA |
---|
A4 |
ABS([FieldA]) = NULL
Since FieldA is not numeric, this formula returns NULL.
The ACOS function calculates the arc-cosine of the specified value (only a decimal value between -1 to 1). This function returns a degree. If the parameter is NULL, then the function returns NULL.
ACOS(<NumericParameter1>)
Aggregate = No
Example 1
FieldA |
---|
0.5 |
ACOS(0.5) = 60
This formula is calculating the arc-cosine value of FieldA.
The ADD function adds the list of specified parameters together. If any of the parameters cannot be converted to a numeric value, then the function returns NULL. NULL parameter values are ignored and do not affect the result value.
ADD(<NumericParameter_1>,<NumericParameter_2>…<NumericParameter_N>)
Aggregate = No
Example
FieldA |
FieldB |
FieldC |
---|---|---|
4 |
6 |
2 |
ADD([FieldA],[FieldB],[FieldC]) = 12
This formula is adding FieldA, FieldB, and FieldC together.
The ASIN function calculates the arc-sine of the specified value (only a decimal value between -1 to 1). This function returns a degree. If the parameter is NULL, then the function returns NULL.
ASIN(<NumericParameter1>)
Aggregate = No
Example 1
FieldA |
---|
0.5 |
Example: ASIN(0.5) = 30
This formula is calculating the arc-sine value of FieldA.
The ATAN function calculates the arc-tan of the specified value (only a decimal value between -1 to 1). This function returns a degree. If the parameter is NULL, then the function returns NULL.
ATAN(<NumericParameter1>)
Aggregate = No
Example 1
FieldA |
---|
0.5 |
Example: ATAN(0.5) = 0.577350269
This formula is calculating the arc-tan value of FieldA.
The AVG function returns the average of all specified parameters in all rows. Null parameter values are ignored and do not affect the result. If all values are NULL or there are no records, the function returns 0. If any parameter value cannot be converted to a numeric value, the function returns NULL.
AVG(<NumericParameter_1>,<NumericParameter_2>…<NumericParameter_N>)
Aggregate = Yes
Example 1 – Collect Form1
FieldA |
---|
4 |
6 |
2 |
AVG([form1.FieldA]) = 4
This formula is calculating the average value of all rows from FieldA on Form1.
Example 2 – Collect Form1
FieldA |
FieldB |
---|---|
4 |
'6' |
6 |
|
|
'4' |
AVG([form1.FieldA],[form1.FieldB]) = 5
This formula is calculating the average value of all rows from FieldA and FieldB on Form1. Note that the string values of FieldB are converted to their numeric equivalent.
Example 3 – Collect Form1
FieldA |
FieldB |
---|---|
4 |
'6' |
6 |
'X' |
|
'4' |
AVG([form1.FieldA],[form1.FieldB]) = NULL
This formula is trying to calculate the average value of all rows from FieldA and FieldB on Form1, but encountered a non-numeric value (‘X’) and therefore returns NULL.
The CEILING function returns the smallest integer value that is greater than or equal to the specified decimal number. If the parameter is not numeric (cannot be converted to a number) or NULL, then the function returns NULL.
CEILING(<NumericParameter_1>)
Aggregate = No
Example 1
FieldA |
---|
15.4985623 |
CEILING([FieldA]) = 16
This formula is returning closest integer value greater than the value in FieldA.
Example 2
FieldA |
---|
8.005012689 |
CEILING([FieldA]) = 9
This formula is returning closest integer value greater than the value in FieldA.
Example 3
FieldA |
---|
8.00 |
CEILING([FieldA]) = 8
This formula is returning the integer value of FieldA.
The COS function calculates the cosine of the specified angle in degrees. If the parameter is NULL or cannot be converted to a numeric value, then the function returns NULL.
COS(<NumericParameter_1>)
Aggregate = No
Example
FieldA |
---|
15 |
COS([FieldA]) = 0.965925826
This formula is calculating the cosine of FieldA.
The COSH function calculates the hyperbolic cosine of the specified angle. The angle must be in radians. If the parameter cannot be converted to a decimal or is NULL, then the function returns NULL.
COSH(<NumericParameter_1>)
Aggregate = No
Example
FieldA |
---|
0.1 |
COSH([FieldA]) = 1.00500416805580
This formula is calculating the hyperbolic cosine of FieldA.
The CUBICSPLINEMAXX function generates a cubic spline interpolation using the non-null X and Y value pairs and returns the maximum X value within its domain. The first two numeric parameters define the X and Y value pair. An optional third parameter can be applied to define the string border condition as either "Natural" or "Quadratic". If the optional third parameter is not defined, it will default to apply a "Natural" string border condition. This function requires at least three data records to begin interpolation.
CUBICSPLINEMAXX(<NumericParameter_1>,<NumericParameter_2>,{<OptionalParamter_1>})
Aggregate = Yes
Example 1 - Form 1
FieldA |
FieldB |
---|---|
6.92506 |
126.7433 |
10.4417 |
128.2164 |
13.8095 |
119.0968 |
CUBICSPLINEMAXX([Form 1.FieldA],[Form 1.FieldB]) = 9.43195800920748
This formula is interpolating the maximum X value from FieldA and FieldB values in Form1 and is using the “Natural” border string condition.
Example 2 - Form 1
FieldA |
FieldB |
---|---|
6.92506 |
126.7433 |
10.4417 |
128.2164 |
13.8095 |
119.0968 |
CUBICSPLINEMAXX([Form 1.FieldA],[Form 1.FieldB],’Quadratic’) = 9.14453439710244
This formula is interpolating the maximum X value from FieldA and FieldB value in Form1 and is using the “Quadratic” string border condition.
The CUBICSPLINEMAXY function generates a cubic spline interpolation using the non-null X and Y value pairs and returns the maximum Y value within its domain. The first two numeric parameters define the X and Y value pair. An optional third parameter can be applied to define the string border condition as either "Natural" or "Quadratic". If the optional third parameter is not defined, it will default to apply a "Natural" string border condition. This function requires at least three data records to begin interpolation.
CUBICSPLINEMAXY(<NumericParameter_1>,<NumericParameter_2>,{<OptionalParamter_1>})
Aggregate = Yes
Example 1 - Form 1
FieldA |
FieldB |
---|---|
6.92506 |
126.7433 |
10.4417 |
128.2164 |
13.8095 |
119.0968 |
CUBICSPLINEMAXY([Form 1.FieldA],[Form 1.FieldB]) = 128.778045294793
This formula is interpolating the maximum Y value from FieldA and FieldB values in Form1 and is using the “Natural” border string condition.
Example 2 - Form 1
FieldA |
FieldB |
---|---|
6.92506 |
126.7433 |
10.4417 |
128.2164 |
13.8095 |
119.0968 |
CUBICSPLINEMAXY([Form 1.FieldA],[Form 1.FieldB],’Quadratic’) = 128.980620733472
This formula is interpolating the maximum Y value from FieldA and FieldB value in Form1 and is using the “Quadratic” string border condition.
The CUBICSPLINEVALUE function generates a cubic spline interpolation using the non-null X and Y value pairs as the first two numeric parameters and returns the function’s Y value when evaluated on a given X value as defined by the third numeric parameter. An optional fourth parameter can be applied to define the string border condition as either "Natural" or "Quadratic". If the optional fourth parameter is not defined, it will default to apply a "Natural" string border condition. This function requires at least three data records to begin interpolation.
CUBICSPLINEVALUE(<NumericParameter_1>,<NumericParameter_2>,<NumericParameter_3>,{<OptionalParamter_1>})
Aggregate = Yes
Example 1 - Form 1
FieldA |
FieldB |
---|---|
6.92506 |
126.7433 |
10.4417 |
128.2164 |
13.8095 |
119.0968 |
CUBICSPLINEVALUE([Form 1.FieldA],[Form 1.FieldB],17.6381) = 109.585498690304
This formula is interpolating the Y value from FieldA and FieldB values in Form1 based on the defined X value of 17.6381, and is using the “Natural” border string condition.
Example 2 - Form 1
FieldA |
FieldB |
---|---|
6.92506 |
126.7433 |
10.4417 |
128.2164 |
13.8095 |
119.0968 |
CUBICSPLINEVALUE([Form 1.FieldA],[Form 1.FieldB],17.6381) = 96.2157801462947
This formula is interpolating the Y value from FieldA and FieldB values in Form1 based on the defined X value of 17.6381, and is using the “Quadratic” border string condition.
The DIVIDE function returns the division of two specified parameters (x/y). If the parameters cannot be converted to a decimal, are NULL or if the second parameter is 0, then the function returns NULL.
DIVIDE(<NumericParameter_1>,<NumericParameter_2>)
Aggregate = No
Example 1
FieldA |
FieldB |
---|---|
4 |
2 |
DIVIDE([FieldA],[FieldB]) = 2
This formula is dividing FieldA by FieldB.
Example 2
FieldA |
FieldB |
---|---|
4 |
0 |
DIVIDE([FieldA],[FieldB]) = NULL
This formula is trying to divide FieldA by FieldB but since FieldB is 0, it returns NULL.
The EXP function calculates the value of e raised to a specified power (ex). If the parameter is NULL or cannot be converted to a decimal, then the function returns NULL.
EXP(<NumericParameter_1>)
Aggregate = No
Example
FieldA |
---|
4 |
EXP([FieldA]) = 54.5981500331
This formula is calculating the mathematical constant, e, raised to the power of FieldA.
The FLOOR function returns the largest integer less than or equal to the specified decimal number. If the parameter cannot be converted to a numeric value or is NULL, then the function returns NULL.
FLOOR(<NumericParameter_1>)
Aggregate = No
Example 1
FieldA |
---|
15.4985623 |
FLOOR([FieldA]) = 15
This formula is returning closest integer value less than the value in FieldA.
Example 2
FieldA |
---|
8.005012689 |
FLOOR([FieldA]) = 8
This formula is returning closest integer value less than the value in FieldA.
The LOG function calculates the logarithm of a specified parameter in a specified base (i.e., NumericParameter_2). If either of the two parameters are NULL or cannot be converted to a numeric value, then the function returns NULL.
LOG(<NumericParameter_1>,<NumericParameter_2>)
Aggregate = No
Example
FieldA |
---|
100 |
LOG([FieldA],10) = 2
This formula is calculating the logarithm in base 10 of FieldA.
The MAX function returns the maximum value found for all specified parameters in all rows. Null parameter values are ignored and do not affect the result. If all values are NULL or there are no records, the function returns NULL. Values are converted to a common type before comparison. For example, if after processing a few rows the maximum value is 10 and then a string value is encountered, the string representation of ‘10’ will be compared against that string value.
MAX(<NumericParameter_1>,<NumericParameter_2>…<NumericParameter_N>)
Aggregate = Yes
Example 1 – Collect Form1
FieldA |
FieldB |
FieldC |
---|---|---|
12 |
0.9 |
108 |
37 |
44 |
67 |
0.7 |
131 |
23 |
MAX([form1.FieldA],[form1.FieldB],[form1.FieldC]) = 131
This formula is calculating the maximum value of all rows from FieldA, FieldB, and FieldC on Form1.
Example 2 – Collect Form1
FieldA |
FieldB |
---|---|
1 |
'1' |
2 |
'4' |
3 |
'04' |
MAX([form1.FieldA],[form1.FieldB]) = 4
This formula is calculating the maximum value of all rows from FieldA and FieldB on Form1. Each comparison is valid as a numeric comparison. So the function is comparing values as numbers.
Example 3 – Collect Form1
FieldA |
FieldB |
---|---|
1 |
'1' |
2 |
'4' |
3 |
'A4' |
MAX([form1.FieldA],[form1.FieldB]) = ‘A4’
This formula is calculating the maximum value of all rows from FieldA and FieldB on Form1. Each comparison is valid as a numeric comparison except for the last one, which compares 4 (the largest value at that point) against ‘A4’, which is string. So the final comparison takes the largest value between ‘4’ and ‘A4’.
The MAXVAL function gets the largest parameter out of a list of parameters. This function can be used with any parameter type and ignores null values. If all values are NULL, then it returns NULL. If strings are used, then the comparison for that value is a string comparison.
MAXVAL(<NumericParameter_1>,<NumericParameter_2>…<NumericParameter_N>)
Aggregate = No
Example 1
FieldA |
FieldB |
FieldC |
FieldD |
FieldE |
---|---|---|---|---|
8 |
14 |
60 |
|
38 |
MAXVAL([FieldA],[FieldB],[FieldC],[FieldD],[FieldE]) = 60
This formula is calculating the maximum value from FieldA, FieldB, FieldC, FieldD, and FieldE.
Example 2
FieldA |
FieldB |
FieldC |
FieldD |
FieldE |
---|---|---|---|---|
'A8' |
14 |
60 |
|
38 |
MAXVAL([FieldA],[FieldB],[FieldC],[FieldD],[FieldE]) = ‘A8’
This formula is calculating the maximum value from FieldA, FieldB, FieldC, FieldD, and FieldE using string comparison since FIeldA is a string value.
The MIN function returns the minimum value found for all specified parameters in all rows. Null parameter values are ignored and do not affect the result. If all values are NULL or there are no records, the function returns NULL. Values are converted to a common type before comparison. For example, if after processing a few rows the minimum value is 10 and then a string value is encountered, the string representation of ‘10’ will be compared against that string value.
MIN(<NumericParameter_1>,<NumericParameter_2>…<NumericParameter_N>)
Aggregate = Yes
Example – Collect Form1
FieldA |
FieldB |
FieldC |
---|---|---|
12 |
0.9 |
108 |
37 |
44 |
67 |
0.7 |
131 |
23 |
MIN([form1.FieldA],[form1.FieldB],[form1.FieldC]) = 0.7
This formula is calculating the minimum value of all rows from FieldA, FieldB, and FieldC.
The MINVAL function gets the smallest parameter out of a list of parameters. This function can be used with any parameter type and ignores null values. If all values are NULL, then it returns NULL. If strings are used, then the comparison for that value is a string comparison.
MINVAL(<NumericParameter_1>,<NumericParameter_2>…<NumericParameter_N>)
Aggregate = No
Example
FieldA |
FieldB |
FieldC |
FieldD |
FieldE |
---|---|---|---|---|
8 |
14 |
60 |
|
38 |
MINVAL([FieldA],[FieldB],[FieldC],[FieldD],[FieldE]) = 8
This formula is calculating the minimum value from FieldA, FieldB, FieldC, FieldD, and FieldE.
The MULTIPLY function calculates the product of the specified parameters. If any of the parameters are NULL or cannot be converted to a number, then the function returns NULL.
MULTIPLY(<NumericParameter_1>,<NumericParameter_2>…<NumericParameter_N>)
Aggregate = No
Example
FieldA |
FieldB |
FieldC |
---|---|---|
2 |
5 |
3 |
MULTIPLY([FieldA],[FieldB],[FieldC]) = 30
This formula is calculating the product of FieldA, FieldB, and FieldC.
The POWER function calculates the value of a specified parameter raised to a specified power (i.e., NumericParameter_2). If any of the parameters are NULL or cannot be converted to a numeric value, then the function returns NULL.
POWER(<NumericParameter_1>,<NumericParameter_2>)
Aggregate = No
Example
FieldA |
---|
4 |
POWER([FieldA],2) = 16
This formula is calculating FieldA raised to the power 2, as specified by the second parameter.
The PRODUCT function calculates the product of all the specified parameters in all rows. NULL values are ignored but if any of the values cannot be converted to a numeric value or if all values are NULL or if there are no rows, then the function returns NULL.
PRODUCT(<NumericParameter_1>,<NumericParameter_2>…<NumericParameter_N>)
Aggregate = Yes
Example – Collect Form1
FieldA |
FieldB |
---|---|
3 |
1 |
2 |
4 |
6 |
2 |
PRODUCT([form1.FieldA],[form1.FieldB]) = 288
This formula is calculating the product of all rows from FieldA and FieldB on Form1.
The RAND function generates a random number. This function can take no parameters or 1 parameter. With no parameters or a null or empty parameter, the function returns a decimal number between 0 and 1. When a parameter is applied that can be converted to an integer value (decimal values are rounded), the function returns a random integer between 0 (inclusive) and N (non-inclusive), where N is equal to the parameters assigned integer value.
RAND({<OptionalNumericParameter_1>})
Aggregate = No
Example 1
FieldA |
---|
|
RAND() = 0.88183345326598
This formula is returning a random decimal, 0.88183345326598, greater than or equal to 0 and less than 1.
Example 2
FieldA |
---|
12.47 |
RAND([FieldA]) = 9
This formula is returning a random integer, 9 in this case, between 0 (inclusive) and 13 (non-inclusive).
The ROUND function rounds a specified parameter to a specified number of fractional digits (i.e., NumericParameter_2). If either of the two parameters are NULL or cannot be converted to numeric values, then the function returns NULL. The second parameter will be converted to an integer value.
ROUND(<NumericParameter_1>,<NumericParameter_2>)
Aggregate = No
Example 1
FieldA |
---|
10.3508 |
ROUND([FieldA],1) = 10.4
This formula is rounding the FieldA value to 1 fractional digit, as specified by the second parameter.
Example 2
FieldA |
---|
10.3508 |
ROUND([FieldA],3) = 10.351
This formula is rounding the FieldA value to 3 fractional digits, as specified by the second parameter.
The SIN function calculates the sine of the specified angle in degrees. If the parameter cannot be converted to a numeric value or is NULL, then the function returns NULL.
SIN(<NumericParameter_1>)
Aggregate = No
Example
FieldA |
---|
15 |
SIN([FieldA]) = 0.25881904510252
This formula is calculating the sine of FieldA.
The SINH function calculates the hyperbolic sine of the specified angle. The angle must be in radians. If the parameter is NULL or cannot be converted to a numeric value, then the function returns NULL.
SINH(<NumericParameter_1>)
Aggregate = No
Example
FieldA |
---|
0.1 |
SINH([FieldA]) = 0.10016675001984403
This formula is calculating the hyperbolic sine of FieldA.
The SQRT function calculates the square root of a specified parameter.
SQRT(<NumericParameter_1>)
Aggregate = No
Example
FieldA |
---|
4 |
SQRT([FieldA]) = 2
This formula is calculating the square root of FieldA.
The SUBTRACT function returns the value of the first parameter minus the total value of the remaining parameters. If any of the parameters cannot be converted to a numeric value or are NULL, then the function returns NULL.
SUBTRACT(<NumericParameter_1>,<NumericParameter_2>…<NumericParameter_N>)
Aggregate = No
Example
FieldA |
FieldB |
FieldC |
---|---|---|
6 |
1 |
3 |
SUBTRACT([FieldA],[FieldB],[FieldC]) = 2
This formula is subtracting FieldB and FieldC from FieldA.
The SUM function calculates the sum of all the specified parameters in all rows. NULL values are ignored but if there are no rows or values cannot be converted to numeric values, then the function returns NULL.
SUM(<NumericParameter_1>,<NumericParameter_2>…<NumericParameter_N>)
Aggregate = Yes
Example – Collect Form1
FieldA |
FieldB |
---|---|
3 |
1 |
2 |
4 |
6 |
2 |
SUM([form1.FieldA],[form1.FieldB]) = 18
This formula is adding all rows of FieldA and FieldB on Form1 together.
The TAN function calculates the tangent of the specified angle in degrees. If the parameter cannot be converted to a numeric value or is NULL, then the function returns NULL.
TAN(<NumericParameter_1>)
Aggregate = No
Example
FieldA |
---|
15 |
TAN([FieldA]) = 0.2679491924311227
This formula is calculating the tangent of FieldA.
The TANH function calculates the hyperbolic tangent of the specified angle. The angle must be in radians. If the parameter cannot be converted to a numeric value or is NULL, then the function returns NULL.
TANH(<NumericParameter_1>)
Aggregate = No
Example
FieldA |
---|
0.1 |
TANH([FieldA]) = 0.09966799462495582
This formula is calculating the hyperbolic tangent of FieldA.
Copyright © 2022 EarthSoft, Inc • Modified: 05 Oct 2022