Formula Language

Overview Of DigiFi Formulas

DigiFi's formula language is powerful tool that lets you write complex calculations using simple syntax and Excel-like functions. It is used through the DigiFi platform for a number of purposes, including:

Calculation modules within decision strategies.
Calculated variables within loan products.
Display conditions in the lender application views, borrower profile, intermediary profile and application form.
Status rules in workflow statuses.
... and more!

Formulas in DigiFi are simple functions that look comparable to spreadsheet functions, for example in Microsoft Excel or Google Sheets. They can include mathematical operators and built-in functions, and can reference variables (i.e. data fields) within the calculations.

Basic Lexical Constructions

DigiFi's formula language consists of few basic lexical constructions, including:

Identifiers

You can access variables within formulas by using their system name.

borrower_first_name + borrower_last_name

Operators

Operator	Symbol	Example
Add, Concat	-	`system_variable + system_variable_2`
Subtract	-	`system_variable - system_variable_2`
Multiply	*	`system_variable * system_variable_2`
Divide	/	`system_variable / system_variable_2`
Divide And Floor	//	`system_variable // sytem_variable_2`
Equal	==	`system_variable == 10` `system_variable == system_variable_2` `system_variable == null` `system_variable == true`
Greater Than	>	`system_variable > 10` `system_variable > system_variable_2`
Greater Than Or Equal	> =	`system_variable >= 10` `system_variable >= system_variable_2`
Modulus	%	`system_variable % system_variable_2`
Negate	!	`!system_variable`
Power Of	^	`system_variable ^ 2` `system_variable ^ system_variable_2`
Less than	<	`system_variable < 10` `system_variable < system_variable_2`
Less than or equal	<=	`system_variable <= 10` `system_variable <= system_variable_2`
Logical And	&&	`system_variable && system_variable_2`
Logical Or	\|\|	`system_variable \|\| system_variable_2`
Not Equal	!=	`system_variable != 10` `system_variable != system_variable_2` `system_variable != null` `system_variable != true`

Ternary Operators

Conditional expressions check to see if the first segment evaluates to a truthy value. If so, the consequent segment is evaluated. Otherwise, the alternate is. If the consequent section is missing, the test result itself will be used instead.

Expression	Result
`"" ? "Full" : "Empty"`	`"Empty"`
`system_name == 10 ? "Yes" : "No"` where `system_name` equals to `20`	`"No"`

Native Types

Type	Example
Boolean	`true`, `false`
String	`"some_string"`
Numeric	`10`, `20`, `100000`, `-5000`
Arrays	`['hello', 'world!']`

Groups

Expression	Result
`(10 + 2) / 2`	`6`
`system_variable && (system_variable_2		system_variable_3)`where`system_variable`equals to`10`,` system_variable_2`equals to`null`and`system_variable_3`equals to`40`	`40`

Excel-Like Functions

DigiFi's formula language includes many commonly-used functions, including:

Date Functions

Function	Description	Format	Example
DATE	Returns a full date based on date inputs (in MM/DD/YYYY format for Date variables and in UTC format for Text variables)	DATE(year, month, day)	DATE(1980, 4, 23) == "04/23/1980")
DATEDIF	Return difference between two dates in days, months or years (based on Unit Type: D, M, Y)	DATEDIF(firstDate, secondDate, unitType)	DATEDIF("01/12/2022", "03/12/2022", "D") == 2
DATEVALUE	Returns the UTC date format based on a date input as MM/DD/YYYY (only assign these results to variables with the data type of "Text"; assigning to "Date" variables will convert the result back to "MM/DD/YYYY" format)	DATEVALUE("MM/DD/YYYY")	DATEVALUE("04/23/1980") == "Wed Apr 23 1980 00:00:00 GMT+0000 (Coordinated Universal Time)"
DAY	Converts a date (in "MM/DD/YYYY" or UTC format) to a day of the month	DAY("MM/DD/YYYY")	DAY("12/25/2008") == 25
DAYS	Returns the number of days between two dates (in UTC or MM/DD/YYYY format)	DAYS(endDate, startDate)	DAYS("3/15/2022", "2/1/2022") == 42
DAYS360	Returns the number of days between two dates based on a 360-day year (twelve 30-day months)	DAYS360("MM/DD/YYYY", "MM/DD/YYYY")	DAYS360("01/01/2011", "12/31/2011") == 360
EDATE	Adds a given number of months to a date.	EDATE(date, months)	EDATE("10/15/2020", -5) == "5/15/2020"
EOMONTH	Returns the date of the last day of the month before or after a specified number of months (in MM/DD/YYYY format for Date variables and in UTC format for Text variables)	EOMONTH("MM/DD/YYYY", months)	EOMONTH("12/01/2020", 1) == "01/31/2021"
ISOWEEKNUM	Returns the number of the ISO week number of the year for a given date (in UTC or MM/DD/YYYY format)	ISOWEEKNUM("MM/DD/YYYY")	ISOWEEKNUM("12/25/2020") == 52
MONTH	Converts a date(in UTC or MM/DD/YYYY format) to a month number	MONTH("MM/DD/YYYY")	MONTH("12/25/2008") == 12
NETWORKDAYSINTL	Returns the number of whole workdays (M-F) between two dates(in UTC or MM/DD/YYYY format)	NETWORKDAYSINTL(start_date, end_date) == 31	NETWORKDAYSINTL("2/1/2022", "3/15/2022") == 31
NOW	Returns the current date and time (in MM/DD/YYYY format for Date variables and in UTC format for Text variables)	NOW()	NOW() == "05/16/2022"
TODAY	Returns the current date and time (in MM/DD/YYYY format for Date variables and in UTC format for Text variables)	TODAY()	TODAY() == "05/16/2022"
WEEKNUM	Converts a date to a number representing where the week falls numerically with a year	WEEKNUM("MM/DD/YYYY")	WEEKNUM("12/25/2020") == 52
WORKDAYINTL	Returns the date before or after a specified number of workdays (in MM/DD/YYYY format for Date variables and in UTC format for Text variables)	WORKDAYINTL("MM/DD/YYYY", workdays)	WORKDAYINTL("01/01/2020", 10) == "01/15/2020"
YEAR	Converts a date (in UTC or MM/DD/YYYY format) to a year	YEAR("MM/DD/YYYY")	YEAR("7/5/2008") == 2008
YEARFRAC	Returns the year fraction representing the number of whole days between start date and end date (in UTC or MM/DD/YYYY format)	YEARFRAC("MM/DD/YYYY", "MM/DD/YYYY")	YEARFRAC("01/01/2012", "07/30/2012", 3) == 0.578

Financial Functions

Function	Description	Format	Example
ACCRINT	Returns the accrued interest for a security that pays periodic interest (dates in MM/DD/YYYY format)	ACCRINT(issue_date, first_interest_date, settlement_date, interest_rate, par_value, frequency, basis)	ACCRINT("01/01/2011", "02/01/2011", "07/01/2014", 0.1, 1000, 1, 0) == 350
CUMIPMT	Returns the cumulative interest paid between two periods	CUMIPMT(interest_rate, number_of_periods, present_value, start_period_number, end_period_number, type)	CUMIPMT(0.01, 360, 100000, 13, 24, 0) == -11934.45
CUMPRINC	Returns the cumulative principal paid on a loan between two periods	CUMPRINC(interest_rate, number_of_periods, present_value, start_period_number, end_period_number, type)	CUMPRINC(0.01, 360, 100000, 13, 24, 0) == -408.902
EFFECT	Returns the effective annual interest rate	EFFECT(interest_rate, npery)	EFFECT(0.1, 4) == 0.104
FV	Returns the future value of an investment	FV(interest_rate, number_of_periods, payment_amount, present_value, type)	FV(0.01, 10, (-100), (-1000), 0) == 2150.843
IPMT	Returns the interest payment for an investment for a given period	IPMT(interest_rate, start_period, total_periods, present_value, future_value, type)	IPMT(0.01, 6, 24, 100000, 1000000, 0) == 1080.231
ISPMT	Calculates the interest paid during a specific period of an investment	ISPMT(interest_Rate, start_period, total_periods, present_value)	ISPMT(0.01, 6, 24, 100000) == -750
NOMINAL	Returns the annual nominal interest rate	NOMINAL(effective_rate, number_of_periods)	NOMINAL(0.1, 4) == 0.096
NPER	Returns the number of periods for an investment	NPER(interest_rate, payment_amount, present_value, future_value, type)	NPER(0.01, (-100), (-1000), 10000, 0) == 60.082
NPV	Returns the net present value of an investment based on a series of periodic cash flows and a discount rate	NPV(discount_rate, value1, value2, …)	NPV(0.1, (-10000), 2000, 4000, 8000) == 1031.350
PDURATION	Returns the number of periods required by an investment to reach a specified value	PDURATION(interest_rate, present_value, future_value)	PDURATION(0.1, 1000, 2000) == 7.273
PMT	Returns the periodic payment for an annuity	PMT(interest_rate, number_periods, present_value, future_value, type)	PMT(0.01, 24, 100000, 1000000, 0) == -41780.819
PPMT	Returns the payment on the principal for an investment for a given period	PPMT(interest_rate, start_period, total_periods, present_value, future_value, type)	PPMT(0.01, 6, 24, 100000, 1000000, 0) == -42861.05
PV	Returns the present value of an investment	PV(discount_rate, number_periods, payment_amount, future_value, type)	PV(0.01, 24, 1000, 10000, 0) == -29119.04
RATE	Returns the interest rate per period of an annuity	RATE(number_periods, payment_amount, present_value, future_value, type)	RATE(24, (-1000), (-10000), 100000, 0) == 0.065

Logical Functions

Function	Description	Format	Example
AND	Returns TRUE if all of its arguments are TRUE	AND(logical1, logical2, …)	AND(1==1, 2==2) == true
EQUAL	Returns TRUE if value equals to another value (with type coercion)	EQUAL(value, valueToCompare)	EQUAL(20, "20") == true
GT	Returns TRUE if value greater than another value (with type coercion)	GT(value, valueToCompare)	GT(20, "19") == true
GTE	Returns TRUE if value greater or equal than another value (with type coercion)	GTE(value, valueToCompare)	GTE(20, "20") == true
IF	Specifies a logical test to perform	IF(logical_test, value_if_true, value_if_false)	IF(3>2, "bigger", "smaller") == "bigger"
IFS	Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition.	IFS(logical_test1, value_if_true1, logical_test2, value_if_true2)	IFS(false, "Hello!", true, "Goodbye") == "Goodbye!"
INCLUDES	Returns TRUE if a text string is contained within an array of strings.	INCLUDES("text", ["test_text1", "test_text2", …])	INCLUDES("apple", ["orange", "banana", "apple"]) == true
ISBLANK	Return TRUE if the value is blank string	ISBLANK(value)	ISBLANK("") == true
ISDATESTRING	Return TRUE if the value is date string	ISDATESTRING(value)	ISDATESTRING("01/02/2022") == true
ISEMPTY	Returns TRUE if the value is null or undefined	ISEMPTY(value)	ISEMPTY("12") == false
ISEMPTYORBLANK	Returns TRUE if the value is null, undefined or empty string	ISEMPTYORBLANK(value)	ISEMPTYORBLANK("") == true
ISEVEN	Returns TRUE if the number is even	ISEVEN(value)	ISEVEN(4) == true
ISFINITE	Returns TRUE if the value is finite	ISFINITE(value)	ISFINITE(24) == true
ISNAN	Return TRUE if the value is not a number	ISNAN(value)	ISNAN("NaN") == true
ISNONTEXT	Returns TRUE if the value is not text	ISNONTEXT(value)	ISNONTEXT(987) == true
ISNOTEMPTY	Return TRUE if the value is not null or undefined	ISNOTEMPTY(value)	ISNOTEMPTY("12") == false
ISNOTEMPTYORBLANK	Returns TRUE if the value is not null, undefined or empty string	ISNOTEMPTYORBLANK(value)	ISNOTEMPTYORBLANK("123") == true
ISNOTNULL	Returns TRUE if the value is not null	ISNOTNULL(value)	ISNOTNULL("123") == true
ISNOTUNDEFINED	Returns TRUE if the value is not undefined	ISNOTUNDEFINED(value)	ISNOTUNDEFINED("test") == true
ISNULL	Returns TRUE if the value is null	ISNULL(value)	ISNULL("123") == false
ISNUMBER	Returns TRUE if the value is a number	ISNUMBER(value)	ISNUMBER(987) == true
ISODD	Returns TRUE if the number is odd	ISODD(value)	ISODD(11) == true
ISTEXT	Returns TRUE if the value is text	ISTEXT(value)	ISTEXT(987) == false
ISUNDEFINED	Returns TRUE if the value is undefined	ISUNDEFINED(value)	ISUNDEFINED("12") == false
LT	Returns TRUE if value lower than another value (with type coercion)	LT(value, valueToCompare)	LT(20, "24") == true
LTE	Returns TRUE if value lower or equal than another value (with type coercion)	LTE(value, valueToCompare)	LTE(20, 20) == true
NINCLUDES	Returns TRUE if a text string is not contained within an array of strings.	NINCLUDES("text", ["test_text1", "test_text2", …])	NINCLUDES("apple", ["orange", "banana", "apple"]) == false
NOT	Reverses the logic of its argument	NOT(logical)	NOT(1==1) == false
NOTEQUAL	Return TRUE if value not equal to another value (with type coercion)	NOTEQUAL(value, valueToCompare)	NOTEQUAL(100, 101) == true
OR	Returns TRUE if any argument is TRUE	OR(logical1, logical2, …)	OR(1==2, 2==2) == true
RANGE	Returns TRUE if a number or date is between two specified values (inclusive of endpoints)	RANGE("test_value", "start_value", "end_value")	RANGE(12, 6, 18) == true
SWITCH	Evaluates an expression against a list of values and returns the result corresponding to the first matching value. If there is no match, an optional default value may be returned.	SWITCH(expression, value1, result1, default_or_value2, result2, …)	SWITCH(7, 9, "Nine", 7, "Seven") == "Seven"
UNIQUE	Returns only unique items for provided array or list	UNIQUE(value)	UNIQUE(["a1", "a1", "a1", "b2", "c3", "d4", "d4", "c3"]) == ["a1", "b2", "c3", "d4"]
XOR	Returns a logical exclusive OR of all arguments	XOR(logical1, logical2, …)	XOR(true, false, true) == false

Mathematical Functions

Function	Description	Format	Example
ABS	Returns the absolute value of a number	ABS(number)	ABS(-3) == 3
ACOS	Returns the arccosine of a number	ACOS(number)	ACOS(-0.5) == 2.094
ACOSH	Returns the inverse hyperbolic cosine of a number	ACOSH(number)	ACOSH(12) == 3.17
ACOT	Returns the arccotangent of a number	ACOT(number)	ACOT(8) == 0.124
ACOTH	Returns the hyperbolic arccotangent of a number	ACOTH(number)	ACOTH(44) == 0.023
ASIN	Returns the arcsine of a number	ASIN(number)	ASIN(-0.5) == -0.524
ASINH	Returns the inverse hyperbolic sine of a number	ASINH(number)	ASINH(-2.5) == -1.647
ATAN	Returns the arctangent of a number	ATAN(number)	ATAN(2) == 1.1071487177940904
ATAN2	Returns the arctangent from x- and y-coordinates	ATAN2(x_num, y_num)	ATAN2(2, 6) == 0.3217505543966422
ATANH	Returns the inverse hyperbolic tangent of a number	ATANH(number)	ATANH(-0.2) == -0.203
AVERAGE	Returns the average of its arguments	AVERAGE(number1, number2, ...)	AVERAGE(1,2,3,4) == 2.5
AVERAGEA	Returns the average of its arguments, including numbers, text, and logical values	AVERAGEA(value1, value2, ...)	AVERAGEA(1,2,3,4, "apple") == 2
AVERAGEIF	Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria	AVERAGEIF([criteria_value1, criteria_value2, ...], criteria, [average_value1, average_value2, …])	AVERAGEIF([2,4,8,16], ">5", [1, 2, 3, 4]) == 3.5
BASE	Converts a number into a text representation with the given radix (base)	BASE(number, radix, min_length)	BASE(15, 2, 10) == "0000001111"
COS	Returns the cosine of a number	COS(number)	COS(12) == 0.844
COSH	Returns the hyperbolic cosine of a number	COSH(number)	COSH(12) == 81377.396
COT	Returns the contangent of an angle	COT(number)	COT(42) == 0.436
COTH	Returns the hyperbolic cotangent of an angle	COTH(number)	COTH(42) == 1
COUNT	Counts how many numbers are in a list of arguments	COUNT(value1, value2, …)	COUNT(12, 14, 16, "apple") == 3
COUNTA	Counts how many values are in the list of arguments	COUNTA(value1, value2, …)	COUNTA(12, 14, 16, "apple") == 4
COUNTBLANK	Counts the number of blank cells within a range	COUNTBLANK(value1, value2, …)	COUNTBLANK("", 14, 16, "apple") == 1
COUNTIF	Counts the number of values within a range that meet the given criteria	COUNTIF([value1, value2, …], criteria)	COUNTIF(["John", "Andrew", "Alice"], "Alice") == 1
COUNTUNIQUE	Counts the number of unique values within a range of values	COUNTUNIQUE(value1, value2, …)	COUNTUNIQUE(1,2,2,3,3,3,4) == 4
CSC	Returns the cosecant of an angle	CSC(number)	CSC(12) == -1.8636795977824385
CSCH	Returns the hyperbolic cosecant of an angle	CSCH(number)	CSCH(42) == 1.149904452858712e-18
DELTA	Returns 1 if two numbers are equal (0 otherwise)	DELTA(value1, value2)	DELTA(20,20) == 1
E	Returns the value of e (e.g. 2.718..)	E()	E() == 2.718281828459045
EVEN	Rounds a number up to the nearest even integer	EVEN(number)	EVEN(3) == 4
EXP	Returns e raised to the power of a given number	EXP(number)	EXP(4) == 54.598
LARGE	Returns the k-th largest value in a data set	LARGE([number1, number2, …], 3)	LARGE([3,5,3,5,4,4,2,4,6,7], 3) == 5
LN	Returns the natural logarithm of a number	LN(number)	LN(12) == 2.485
LOG	Returns the logarithm of a number to a specified base	LOG(number, base)	LOG(12, 10) == 1.079
MAX	Returns the maximum value in a list of arguments	MAX(value1, value2, …)	MAX(12, 24, 13, 82, 1, 92) == 92
MAXA	Returns the maximum value in a list of arguments, including numbers, text, and logical values	MAXA(value1, value2, …)	MAXA(12, 24, 13, 82, 1, 92, "apple") == 92
MEDIAN	Returns the median of the given numbers	MEDIAN(value1, value2, …)	MEDIAN(12, 24, 13, 82, 92) == 24
MIN	Returns the minimum value in a list of arguments	MIN(value1, value2, …)	MIN(12, 24, 13, 82, 1, 92) == 1
MINA	Returns the smallest value in a list of arguments, including numbers, text, and logical values	MINA(value1, value2, …)	MINA(12, 24, 13, 82, 1, 92, "apple") == 0
MOD	Returns the remainder from division	MOD(number, divisor)	MOD(12,5) == 2
MODE	Returns the most common value in a data set	MODE(value1, value2, …)	MODE(12, 12, 24, 13, 82) == 12
ODD	Rounds a number up to the nearest odd integer	ODD(number)	ODD(2) == 3
PI	Returns the value of pi (i.e. 3.142...)	PI()	PI() == 3.142
POWER	Returns the result of a number raised to a power	POWER(number, power)	POWER(12,2) == 144
PRODUCT	Multiplies its arguments	PRODUCT(value1, value2, …)	PRODUCT(4,6,8) == 192
QUOTIENT	Returns the integer portion of a division	QUOTIENT(numerator, denominator)	QUOTIENT(62,11) == 5
RADIANS	Converts degrees to radians	RADIANS(angle)	RADIANS(90) == 1.571
RAND	Returns a random number between 0 and 1	RAND()	RAND() == 0.213
RANDBETWEEN	Returns a random number between the numbers you specify	RANDBETWEEN(bottom, top)	RANDBETWEEN(12, 18) == 14
ROUND	Rounds a number to a specified number of digits	ROUND(number, num_digits)	ROUND(626.321, 2) == 626.32
ROUNDDOWN	Rounds a number down, toward zero	ROUNDDOWN(number, num_digits)	ROUNDDOWN(6.8, 0) == 6
ROUNDUP	Rounds a number up, away from zero	ROUNDUP(number, num_digits)	ROUNDUP(6.2, 0) == 7
SEC	Returns the secant of an angle	SEC(number)	SEC(180) == -1.671
SECH	Returns the hyperbolic secant of an angle	SECH(number)	SECH(180) == 1.343
SIN	Returns the sine of the given angle	SIN(number)	SIN(45) == 0.851
SINH	Returns the hyperbolic sine of a number	SINH(number)	SINH(3) == 10.018
SMALL	Returns the k-th smallest value in a data set	SMALL(array, k)	SMALL([1,2,3,4,5,6], 3) == 3
SQRT	Returns a positive square root	SQRT(number)	SQRT(12) == 3.464
SQRTPI	Returns the square root of (number * pi)	SQRTPI(number)	SQRTPI(12) == 6.14
STANDARDIZE	Returns a normalized value	STANDARDIZE(x, mean, standard_dev)	STANDARDIZE(42, 40, 1.5) == 1.333
SUM	Adds its arguments together	SUM(number1, number2, …)	SUM(10, 20, 11, 14) == 55
SUMIF	Adds the cells specified by a given criteria	SUMIF([number1, number2, ...], logical)	SUMIF([10, 20, 30, 11, 14], ">15") == 50
SUMSQ	Returns the sum of the squares of the arguments	SUMSQ(number1, number2, …)	SUMSQ(3, 4) == 25
SUMX2MY2	Returns the sum of the difference of squares of corresponding values in two arrays	SUMX2MY2(array_x, array_y)	SUMX2MY2([1,2], [3,4]) == -20
SUMX2PY2	Returns the sum of the sum of squares of corresponding values in two arrays	SUMX2PY2(array_x, array_y)	SUMX2PY2([1,2], [3,4]) == 30
SUMXMY2	Returns the sum of squares of differences of corresponding values in two arrays	SUMXMY2(array_x, array_y)	SUMXMY2([1,2], [3,4]) == 8
TAN	Returns the tangent of a number	TAN(number)	TAN(10) == 0.648
TANH	Returns the hyperbolic tangent of a number	TANH(number)	TANH(0.2) == 0.197
TRUNC	Truncates a number to an integer	TRUNC(number, num_digits)	TRUNC(12.222, 1) == 12.2

Text Functions

Function	Description	Format	Example
BUILDFULLADDRESS	Converts address variable to string	BUILDFULLADDRESS(variable)	BUILDFULLADDRESS(variable) == "Some Address"
CLEAN	Removes all nonprintable characters from text	CLEAN("text")	CLEAN("mytext") == "mytext"
CONCAT	Combines the text from strings into a single string	CONCAT("text1", "text2", …)	CONCAT("apple", ", ", "orange") == "apple, orange"
CONCATENATE	Combines the text from strings into a single string	CONCATENATE("text1", "text2", …)	CONCATENATE("apple", ", ", "orange") == "apple, orange"
EXACT	Checks to see if two text values are identical (case sensitive)	EXACT(value1, value2)	EXACT("apple", "apple") == true
FIND	Finds one text value within another (case-sensitive)	FIND("find_text", "within_text", start_num)	FIND("M", "Miriam McGovern", 3) == 8
LEFT	Returns the leftmost characters from a text value	LEFT(value, num_chars)	LEFT("apple", 3) == "app"
LEN	Returns the number of characters in a text string	LEN(value)	LEN("apple") == 5
LOWER	Converts text to lowercase	LOWER("text")	LOWER("APPLE") == "apple"
MID	Returns a specific number of characters from a text string starting at the position you specify	MID(value, start_num, num_chars)	MID("apple", 2, 2) == "pp"
PROPER	Capitalizes the first letter in each word of a text value	PROPER("text")	PROPER("FIX this Sentence") == "Fix This Sentence"
REPLACE	Replaces characters within text	REPLACE("old_text", start_number, number_characters, "new_text")	REPLACE("abcdefghijk", 6, 5, "_") == "abcde_k"
REPT	Repeats text a given number of times	REPT("text", number_times)	REPT("-", 3) == "---"
RIGHT	Returns the rightmost characters from a text value	RIGHT(value, num_chars)	RIGHT("apple", 3) == "ple"
SEARCH	Finds one text value within another (not case-sensitive)	SEARCH(find_text, within_text, start_num)	SEARCH("orange", "apple & orange", 1) == 9
SUBSTITUTE	Substitutes new text for old text in a text string	SUBSTITUTE("text", "old_text", "new_text")	SUBSTITUTE("apple", "p", "a") == "aaale"
TRIM	Removes spaces from text	TRIM("text")	TRIM("apple") == "apple"
UPPER	Converts text to uppercase	UPPER("text")	UPPER("convert THIS text") == "CONVERT THIS TEXT"
VALUE	Converts a text argument to a number	VALUE("text")	VALUE("12") == 12

Table Functions

Function	Description	Format	Example
TABLEAVG	Finds the average value of a column in a table.	TABLEAVG(variable, column_name)	TABLEAVG(income_details, "income")
TABLEAVGIF	Finds the average value of a column in a table if a condition passes.	TABLEAVGIF(variable, column_name, logical_column_name, logical)	TABLEAVGIF(income_details, "income", "jobType", "=Software Engineer")
TABLEAVGIFS	Finds the average value of a column in a table if multiple conditions pass.	TABLEAVGIFS(variable, column_name, logical_column_name1, logical1, logical_column_name2, logical2, ...)	TABLEAVGIFS(income_details, "income", "jobType", "=Software Engineer", "name", "=Harold")
TABLEAVGIFSOR	Finds the average value of a column in a table if at least one condition passes (excluding blank, null or undefined fields).	TABLEAVGIFSOR(variable, column_name, logical_column_name1, logical1, logical_column_name2, logical2, ...)	TABLEAVGIFSOR(income_details, "income", "jobType", "=Software Engineer", "name", "=Harold")
TABLECONCATROWS	Adds new table rows (returns a new table).	TABLECONCATROWS(variable, arrayOfRows)	TABLECONCATROWS(income_details, [{income: "1", name: "Henry", _id: '123'}, {"income: "2", name: "Jack", _id: '456'}])
TABLECOUNT	Counts all rows of a single column (excluding blank, null or undefined fields).	TABLECOUNT(variable, column_name)	TABLECOUNT(income_details, "income")
TABLECOUNTIF	Counts all rows of a single column together if a condition passes (excluding blank, null or undefined fields).	TABLECOUNTIF(variable, column_name, logical_column_name, logical)	TABLECOUNTIF(income_details, "income", "jobType", "=Software Engineer")
TABLECOUNTIFS	Counts all rows of a single column together if multiple conditions pass (excluding blank, null or undefined fields).	TABLECOUNTIFS(variable, column_name, logical_column_name1, logical1, logical_column_name2, logical2, ...)	TABLECOUNTIFS(income_details, "income", "jobType", "=Software Engineer", "name", "=Harold")
TABLECOUNTIFSOR	Counts all rows of a single column together if at least one condition passes (excluding blank, null or undefined fields).	TABLECOUNTIFSOR(variable, column_name, logical_column_name1, logical1, logical_column_name2, logical2, ...)	TABLECOUNTIFSOR(income_details, "income", "jobType", "=Software Engineer", "name", "=Harold")
TABLEFILTERROWSIF	Removes rows from table that do not match a condition (returns a new table).	TABLEFILTERROWSIF(variable, logical_column_name, logical)	TABLEFILTERROWSIF(income_details, "income", "<10")
TABLEFILTERROWSIFS	Removes rows from table that do not match all the conditions (returns a new table).	TABLEFILTERROWSIFS(variable, logical_column_name1, logical1, logical_column_name2, logical2, ...)	TABLEFILTERROWSIFS(income_details, "income", "<10", "name", "=Harold")
TABLEFILTERROWSIFSOR	Removes rows from table if at least one condition passes (returns a new table).	TABLEFILTERROWSIFSOR(variable, logical_column_name1, logical1, logical_column_name2, logical2, ...)	TABLEFILTERROWSIFSOR(income_details, "income", "<10", "name", "=Harold")
TABLEMATCHESCONDITIONS	Checks if table matches one or many conditions and returns true or false.	TABLEMATCHESCONDITIONS(variable, logical_column_name1, logical1, logical_column_name2, logical2, ...)	TABLEMATCHESCONDITION(income_details, "income", ">10", "name", "<>Harold")
TABLEMAX	Finds the maximum value of all rows of a single column.	TABLEMAX(variable, column_name)	TABLEMAX(income_details, "income")
TABLEMAXIF	Finds the maximum value of all rows of a single column for which a condition passes.	TABLEMAXIF(variable, column_name, logical_column_name, logical)	TABLEMAXIF(income_details, "income", "jobType", "=Software Engineer")
TABLEMAXIFS	Finds the maximum value of all rows of a single column for which multiple conditions pass.	TABLEMAXIFS(variable, column_name, logical_column_name1, logical1, logical_column_name2, logical2, ...)	TABLEMAXIFS(income_details, "income", "jobType", "=Software Engineer", "name", "=Harold")
TABLEMAXIFSOR	Finds the maximum value of all rows of a single column for which at least one condition passes.	TABLEMAXIFSOR(variable, column_name, logical_column_name1, logical1, logical_column_name2, logical2, ...)	TABLEMAXIFSOR(income_details, "income", "jobType", "=Software Engineer", "name", "=Harold")
TABLEMIN	Finds the minimum value of all rows of a single column.	TABLEMIN(variable, column_name)	TABLEMIN(income_details, "income")
TABLEMINIF	Finds the minimum value of all rows of a single column for which a condition passes.	TABLEMINIF(variable, column_name, logical_column_name, logical)	TABLEMINIF(income_details, "income", "jobType", "=Software Engineer")
TABLEMINIFS	Finds the minimum value of all rows of a single column for which multiple conditions pass.	TABLEMINIFS(variable, column_name, logical_column_name1, logical1, logical_column_name2, logical2, ...)	TABLEMINIFS(income_details, "income", "jobType", "=Software Engineer", "name", "=Harold")
TABLEMINIFSOR	Finds the minimum value of all rows of a single column for which at least one condition passes.	TABLEMINIFSOR(variable, column_name, logical_column_name1, logical1, logical_column_name2, logical2, ...)	TABLEMINIFSOR(income_details, "income", "jobType", "=Software Engineer", "name", "=Harold")
TABLESUM	Adds all rows of a single column together.	TABLESUM(variable, column_name)	TABLESUM(income_details, "income")
TABLESUMIF	Adds all rows of a single column together if a condition passes.	TABLESUMIF(variable, column_name, logical_column_name, logical)	TABLESUMIF(income_details, "income", "jobType", "=Software Engineer")
TABLESUMIFS	Adds all rows of a single column together if multiple conditions pass.	TABLESUMIFS(variable, column_name, logical_column_name1, logical1, logica_column_name2, logical2, ...)	TABLESUMIFS(income_details, "income", "jobType", "=Software Engineer", "name", "=Harold")
TABLESUMIFSOR	Adds all rows of a single column together if at least one condition passes.	TABLESUMIFSOR(variable, column_name, logical_columnName1, logical1, logical_column_name2, logical2, ...)	TABLESUMIFSOR(income_details, "income", "jobType", "=Software Engineer", "name", "=Harold")

Formatting

Function	Description	Format	Example
MONETARYFORMAT	Returns formatted monetary value.	MONETARYFORMAT(value, fractionDigits = 2, useGrouping = true, currencySymbol = $, currencyPosition = left)	MONETARYFORMAT(11234.567) == "$11,234.57"

General Functions

Function	Description	Format	Example
UUID	Generates universally unique dentifier (UUID) v4.	UUID()	UUID() == 123e4567-e89b-12d3-a456-426655440000