Math Operators
Add
The Add function returns the sum of two numbers and is equivalent to the ‘+’ operator.
Syntax
ARITH([value_1] + [value_2])
Example
ARITH([4] + [4]) = 8
ARITH([4] + [-8]) = -4
Minus
The Minus function returns the difference of two numbers and is equivalent to the ‘–’ operator.
Syntax
ARITH([value_1] - [value_2])
Example
ARITH([8] – [4]) = 4
ARITH([4] - [8]) = -4
Multiply
The Multiply function returns the product of two numbers and is equivalent to the ‘*’ operator.
Syntax
ARITH([value_1]*[value_2])
Example
ARITH([4]*[4]) = 16
ARITH([4]*[-8]) = -32
Divide
The Divide function returns one number divided by another and is equivalent to the ‘/’ operator.
Syntax
ARITH([value_1]/[value_2])
Example
ARITH([4]/[4]) = 1
ARITH([-8]/[4]) = -2
MOD
The MOD function returns arithmetic reminder from integer division.
Syntax
MOD([value1],[value2]
Example
MOD([5],[4]) = 1
MAX
The greatest value/item in the list is returned by MAX function.
Syntax
MAX(list-to-choose-max-value-from)
Example
MAX(LIST(22,50,45)) : 45
MAX(Order[Price]): This represents the greatest value in the Price column of the Order table.
MAX([Quantity]): This represents the greatest value of all items in the Quantity column value, where Quantity is of type List.
Arguments
The list must be numeric. There can be two cases:
Return Value
The greatest value in the list.
MIN
The least value/item in the list is returned by MIN function.
Syntax
MIN(list-to-choose-min-value-from)
Example
MIN(LIST(1,7,66)) : 1
MIN(Order[Price]) : This represents the least value in the Price column of the Order table.
MIN([Quantity]) : This represents the least of all items in the Quantity column value, where Quantity is of type List.
Arguments
The list must be numeric. There can be two cases:
Return Value
The least value in the list.
AVERAGE
The arithmetic average of list items is returned by the AVERAGE function.
Syntax
AVERAGE(LIST())
Example
AVERAGE(LIST(78,4,86)) : 56
AVERAGE(Order[Price]) : This represents the arithmetic average of all values in the Price column of the Order table.
AVERAGE([Quantity]) : This represents the arithmetic average of all items in the Quantity column value, where Quantity is of type List for some numeric type.
AVERAGE(LIST(1, 7, 4)) : 4
AVERAGE(LIST(0) - LIST(0)) (a valid constructed list with a type but no items): 0.00
Arguments
The list must be numeric of any type.
Return Value
Decimal: The computed average of the values of list.
COUNT
The total number of items in the list is returned by the COUNT function.
Syntax
COUNT(list-to-count)
Example
COUNT(LIST(45,54,71,5,6)) : 5
COUNT(Order[Color]) : This represents the total number of (possibly duplicate) values in the Color column of the Order table.
COUNT([Quantity]) : This represents the count of the items in the Quantity column value, where Quantity is of type List.
COUNT(LIST("Red", "Yellow", "Blue")) : 3
COUNT(LIST()) : 0
Arguments
The list must be numeric of any type.
Return Value
Number: The count of items in the list.
Learn more about COUNT
ROUND
The ROUND function returns the closest value to ‘x’ where x can be any number.
Syntax
ROUND(number-to-round)
Example
Round(4.4) : 4
Round(4) : 4
Round(-4.4) : -4
Round(4.8) : 5
Arguments
x (any numeric type).
Return Value
Number: The number nearest x.
SQRT
The square root of the number is returned by SQRT function.
Syntax
SQRT(numeric-value)
Example
SQRT(4) : 2
SQRT(2) : 1.414
SQRT(3) : 1.73
SQRT(2) : 1.41
SQRT(1) : 1.00
SQRT(0) : 0.00
SQRT(-1) (or any negative number): NaN ("not a number")
Arguments
Integer x (any type).
Return Value
Decimal: The square root of x, where x is an integer.
Distance
DISTANCE returns the approximate distance in kilometers between two locations expressed as LatLong values. DISTANCE() does not work with Address values.
Syntax
DISTANCE(location_1, location_2))
Example
DISTANCE('28.5407217,77.3964503','28.5407217,77.3964503')
POWER
The arithmetic exponentiation of the base number is returned by the POWER function.
Syntax
POWER(base, exponent)
Example
POWER(10,2) : 100
POWER(-2.23, 3) : -11.09
POWER(-2.23, 2) : 4.97
POWER(-2.23, 1) : -2.23
POWER(-2.23, 0) : 1.00
POWER(-2.23, -1) : -0.45
POWER(-2.23, -2) : 0.20
POWER(-2.23, -3) : -0.09
Arguments
Integers x and y (of any type).
Return Value
Decimal: x raised to the power of y, where x and y both are integers.
ABS
The arithmetic absolute value of the number is returned by the ABS function.
Syntax
ABS(numeric-value)
Example
ABS(-77) returns Number: 77
ABS(0) returns Number: 0
ABS(1) returns Number: 1
ABS(-1) returns Number: 1
ABS(0.0) returns Decimal: 0
ABS(4.16) returns Decimal: 3.14
ABS(-4.16) returns Decimal: 3.14
Arguments
Integer x (any type)
Return Value
The absolute value of x, where x is an integer.
FLOOR
The nearest integer value less than or equal to x (where x is an integer) is returned by the FLOOR function.
Syntax
FLOOR(number-to-round-down)
Example
FLOOR(10.69) : 10
FLOOR(3.14) : 3
FLOOR(-3.14) : -4
FLOOR(5) : 5
Arguments
Integer x (any type)
Return Value
Number: The nearest integer less than or equal to x, where x is an integer.
CEILING
The CEILING function rounds a number up to the nearest integer multiple of specified significance.
Syntax
CEILING(number-to-round-up)
Example
CEILING(10.98) : 10
CEILING(3.14) : 4
CEILING(-3.14) : -3
CEILING(5) : 5
Arguments
Integer x (any type)
Return Value
Number: The nearest integer greater than or equal to x, where x is an integer.
Learn more about CEILING
Number
Number always returns the number value.
Syntax
NUMBER(value-to-convert-to-number)
Example
NUMBER(10) : 10
DECIMAL
The DECIMAL function returns the exact value.
Syntax
DECIMAL(value-to-convert-to-decimal)
Example
DECIMAL(8.4) : 8.4
DECIMAL(8.0) : 8.0
STDEVP
The arithmetic standard deviation of list items is returned by the STDEVP function.
Syntax
STDEVP(list)
STDEVP(LIST([COLUMN_0],...[COLUMN_N]))
Example
STDEVP(LIST(1, 2, 3)) : 0.84
STDEVP(Order[Price]) : This represents the standard deviation of values in the Price column of the Order table.
STDEVP([Quantity]) : This represents the standard deviation for the items in the Quantity column value, where Quantity is of type List.
STDEVP(LIST(1, 2, 3)) : 0.82
Arguments
The list must be numeric. There can be two cases:
Return Value
Decimal: The computed standard deviation of the values in list.
SUM
The arithmetic sum of list items is returned by the SUM function.
Syntax
SUM(list)
SUM(LIST([COLUMN_0],...[COLUMN_N]))
Example
SUM(LIST(2,10)) : 12
SUM(Classes[Seat Count]) : This represents the sum of all values in the Seat Count column of the Classes table.
SUM([Quantity]) : This represents the sum of the items in the Quantity column value, where Quantity is of type List.
SUM(LIST(1, 2, 4)) : 7
Arguments
The list must be numeric. There can be two cases:
Return Value
The computed sum of the values in some-list.