Formulas

Adding formula column

Formula

1. Click on '+' (Add column)

2. Populate column Name

3. Select column Type as 'Formula'

4. Insert required formula

  • Can use column names in equation
  • Can use explicit numberical values/ strings as needed
  • Table below lists supported formula & associated syntax
  • Nested formula (formula equation referring to another formula column) are not supported

5. Click on 'Save'

Available Formula Features

Functions

NameSyntaxSampleOutputMinimum arguments
AVGAVG(value1, [value2,...])AVG(Column1, Column1)Average of input parameters1
ADDADD(value1, [value2,...])ADD(Column1, Column1)Sum of input parameters1
CONCATCONCAT(value1, [value2,...])CONCAT(FirstName, ' ', LastName)Concatenated string of input parameters
TRIMTRIM(value1)TRIM(Title)Removes trailing and leading whitespaces from input parameter
UPPERUPPER(value1)UPPER(Title)Upper case converted string of input parameter
LOWERLOWER(value1)LOWER(Title)Lower case converted string of input parameter
LENLEN(value)LEN(Title)Input parameter charachter length
MINMIN(value1, [value2,...])MIN(Column1, Column2, Column3)Minimum value amongst input parameters
MAXMAX(value1, [value2,...])MAX(Column1, Column2, Column3)Maximum value amongst input parameters
CEILINGCEILING(value1)CEILING(Column)Rounded next largest integer value of input parameter
FLOORFLOOR(value1)FLOOR(Column)Rounded largest integer less than or equal to input parameter
ROUNDROUND(value1)ROUND(Column)Nearest integer to the input parameter
MODMOD(value1, value2)MOD(Column, 2)Remainder after integer division of input parameters2
REPEATREPEAT(value1, count)REPEAT(Column, 2)Specified copies of the input parameter string concatenated together
LOGLOG(value1, [base])LOG(Column)Logarithm of input parameter to the base specified
EXPEXP(value1)EXP(Column)Exponential value of input parameter (e^x)
POWERPOWER(base, exponent)POWER(Column, 3)base to the exponent power, as in base^exponent
SQRTSQRT(value1)SQRT(Column)Square root of the input parameter
ABSABS(value1)ABS(Column)Absolute value of the input parameter
NOWNOW()NOW()Current date time
REPLACEREPLACE(value1, old_str,new_str)REPLACE(Column, old_string, new_String)String, after replacing all occurrences of old_string with new_String
SEARCHSEARCH(value1, search_val)SEARCH(Column, 'str')Index of sub-string specified if found, 0 otherwise
INTINT(value1)INT(Column)Integer value of input parameter
RIGHTRIGHT(value1, count)RIGHT(Column, 3)n characters from the end of input parameter
LEFTLEFT(value1, [value2,...])LEFT(Column, 3)n characters from the beginning of input parameter
SUBSTRSUBTR(value1, position, [count])SUBSTR(Column, 3, 2)Substring of length 'count' of input string, from the postition specified
MIDSUBTR(value1, position, [count])MID(Column, 3, 2)Alias for SUBSTR
IFIF(expression, success_case, [else_case])IF(Column > 1, Value1, Value2)success_case if expression evaluates to TRUE, else_case otherwise
SWITCHSWITCH(expression, [pattern,value,..., default_value])SWITCH(Column1, 1, 'One', 2, 'Two', '--')Switch case value based on expression output
ANDAND(expression1, [expression2,...])AND(Column > 2, Column < 10)TRUE if all expressions evaluate to TRUE
OROR(expression1, [expression2,...])OR(Column > 2, Column < 10)TRUE if at least one expression evaluates to TRUE

Numeric Operators

OperatorSampleDescription
+column1 + column2 + 2Addition of numeric values
-column1 - column2Subtraction of numeric values
*column1 * column2Multiplication of numeric values
-column1 / column2Division of numeric values

Logical operators

OperatorSampleDescription
<column1 < column2Less than
>column1 > column2Greater than
<=column1 <= column2Less than or equal to
>=column1 >= column2Greater than or equal to
==column1 == column2Equal to
!=column1 != column2Not equal to