How to write formulas in the DigiFi platform.

## 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:

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

OperatorSymbolExample
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.

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

Native Types

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

Groups

ExpressionResult
`(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

FunctionDescriptionFormatExample
DATEReturns 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")
DATEVALUEReturns 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)"
DAYConverts a date (in "MM/DD/YYYY" or UTC format) to a day of the monthDAY("MM/DD/YYYY")DAY("12/25/2008") == 25
DAYSReturns 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
EDATEAdds a given number of months to a date.EDATE(date, months)EDATE("10/15/2020", -5) == "5/15/2020"
EOMONTHReturns 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"
ISOWEEKNUMReturns 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
MONTHConverts a date(in UTC or MM/DD/YYYY format) to a month numberMONTH("MM/DD/YYYY")MONTH("12/25/2008") == 12
NETWORKDAYSINTLReturns the number of whole workdays (M-F) between two dates(in UTC or MM/DD/YYYY format)NETWORKDAYSINTL(start_date, end_date) == 31NETWORKDAYSINTL("2/1/2022", "3/15/2022") == 31
NOWReturns 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"
TODAYReturns 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"
WEEKNUMConverts a date to a number representing where the week falls numerically with a yearWEEKNUM("MM/DD/YYYY")WEEKNUM("12/25/2020") == 52
WORKDAYINTLReturns 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"
YEARConverts a date (in UTC or MM/DD/YYYY format) to a yearYEAR("MM/DD/YYYY")YEAR("7/5/2008") == 2008
YEARFRACReturns 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

FunctionDescriptionFormatExample
ACCRINTReturns 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
CUMIPMTReturns the cumulative interest paid between two periodsCUMIPMT(interest_rate, number_of_periods, present_value, start_period_number, end_period_number, type)CUMIPMT(0.01, 360, 100000, 13, 24, 0) == -11934.45
CUMPRINCReturns the cumulative principal paid on a loan between two periodsCUMPRINC(interest_rate, number_of_periods, present_value, start_period_number, end_period_number, type)CUMPRINC(0.01, 360, 100000, 13, 24, 0) == -408.902
EFFECTReturns the effective annual interest rateEFFECT(interest_rate, npery)EFFECT(0.1, 4) == 0.104
FVReturns the future value of an investmentFV(interest_rate, number_of_periods, payment_amount, present_value, type)FV(0.01, 10, (-100), (-1000), 0) == 2150.843
IPMTReturns the interest payment for an investment for a given periodIPMT(interest_rate, start_period, total_periods, present_value, future_value, type)IPMT(0.01, 6, 24, 100000, 1000000, 0) == 1080.231
ISPMTCalculates the interest paid during a specific period of an investmentISPMT(interest_Rate, start_period, total_periods, present_value)ISPMT(0.01, 6, 24, 100000) == -750
NOMINALReturns the annual nominal interest rateNOMINAL(effective_rate, number_of_periods)NOMINAL(0.1, 4) == 0.096
NPERReturns the number of periods for an investmentNPER(interest_rate, payment_amount, present_value, future_value, type)NPER(0.01, (-100), (-1000), 10000, 0) == 60.082
NPVReturns the net present value of an investment based on a series of periodic cash flows and a discount rateNPV(discount_rate, value1, value2, …)NPV(0.1, (-10000), 2000, 4000, 8000) == 1031.350
PDURATIONReturns the number of periods required by an investment to reach a specified valuePDURATION(interest_rate, present_value, future_value)PDURATION(0.1, 1000, 2000) == 7.273
PMTReturns the periodic payment for an annuityPMT(interest_rate, number_periods, present_value, future_value, type)PMT(0.01, 24, 100000, 1000000, 0) == -41780.819
PPMTReturns the payment on the principal for an investment for a given periodPPMT(interest_rate, start_period, total_periods, present_value, future_value, type)PPMT(0.01, 6, 24, 100000, 1000000, 0) == -42861.05
PVReturns the present value of an investmentPV(discount_rate, number_periods, payment_amount, future_value, type)PV(0.01, 24, 1000, 10000, 0) == -29119.04
RATEReturns the interest rate per period of an annuityRATE(number_periods, payment_amount, present_value, future_value, type)RATE(24, (-1000), (-10000), 100000, 0) == 0.065

### Logical Functions

FunctionDescriptionFormatExample
ANDReturns TRUE if all of its arguments are TRUEAND(logical1, logical2, …)AND(1==1, 2==2) == true
IFSpecifies a logical test to performIF(logical_test, value_if_true, value_if_false)IF(3>2, "bigger", "smaller") == "bigger"
IFSChecks 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!"
INCLUDESReturns TRUE if a text string is contained within an array of strings.INCLUDES("text", ["test_text1", "test_text2", …])INCLUDES("apple", ["orange", "banana", "apple"]) == true
ISBLANKReturn TRUE if the value is blank stringISBLANK(value)ISBLANK("") == true
ISDATESTRINGReturn TRUE if the value is date stringISDATESTRING(value)ISDATESTRING("01/02/2022") == true
ISEMPTYReturns TRUE if the value is null or undefinedISEMPTY(value)ISEMPTY("12") == false
ISEVENReturns TRUE if the number is evenISEVEN(value)ISEVEN(4) == true
ISFINITEReturns TRUE if the value is finiteISFINITE(value)ISFINITE(24) == true
ISNANReturn TRUE if the value is not a numberISNAN(value)ISNAN("NaN") == true
ISNONTEXTReturns TRUE if the value is not textISNONTEXT(value)ISNONTEXT(987) == true
ISNOTEMPTYReturn TRUE if the value is not null or undefinedISNOTEMPTY(value)ISNOTEMPTY("12") == false
ISNOTNULLReturns TRUE if the value is not nullISNOTNULL(value)ISNOTNULL("123") == true
ISNOTUNDEFINEDReturns TRUE if the value is not undefinedISNOTUNDEFINED(value)ISNOTUNDEFINED("test") == true
ISNULLReturns TRUE if the value is nullISNULL(value)ISNULL("123") == false
ISNUMBERReturns TRUE if the value is a numberISNUMBER(value)ISNUMBER(987) == true
ISODDReturns TRUE if the number is oddISODD(value)ISODD(11) == true
ISTEXTReturns TRUE if the value is textISTEXT(value)ISTEXT(987) == false
ISUNDEFINEDReturns TRUE if the value is undefinedISUNDEFINED(value)ISUNDEFINED("12") == false
NINCLUDESReturns 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
NOTReverses the logic of its argumentNOT(logical)NOT(1==1) == false
ORReturns TRUE if any argument is TRUEOR(logical1, logical2, …)OR(1==2, 2==2) == true
RANGEReturns 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
SWITCHEvaluates 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"
UNIQUEReturns only unique items for provided array or listUNIQUE(value)UNIQUE(["a1", "a1", "a1", "b2", "c3", "d4", "d4", "c3"]) == ["a1", "b2", "c3", "d4"]
XORReturns a logical exclusive OR of all argumentsXOR(logical1, logical2, …)XOR(true, false, true) == false

### Mathematical Functions

FunctionDescriptionFormatExample
ABSReturns the absolute value of a numberABS(number)ABS(-3) == 3
ACOSReturns the arccosine of a numberACOS(number)ACOS(-0.5) == 2.094
ACOSHReturns the inverse hyperbolic cosine of a numberACOSH(number)ACOSH(12) == 3.17
ACOTReturns the arccotangent of a numberACOT(number)ACOT(8) == 0.124
ACOTHReturns the hyperbolic arccotangent of a numberACOTH(number)ACOTH(44) == 0.023
ASINReturns the arcsine of a numberASIN(number)ASIN(-0.5) == -0.524
ASINHReturns the inverse hyperbolic sine of a numberASINH(number)ASINH(-2.5) == -1.647
ATANReturns the arctangent of a numberATAN(number)ATAN(2) == 1.1071487177940904
ATAN2Returns the arctangent from x- and y-coordinatesATAN2(x_num, y_num)ATAN2(2, 6) == 0.3217505543966422
ATANHReturns the inverse hyperbolic tangent of a numberATANH(number)ATANH(-0.2) == -0.203
AVERAGEReturns the average of its argumentsAVERAGE(number1, number2, ...)AVERAGE(1,2,3,4) == 2.5
AVERAGEAReturns the average of its arguments, including numbers, text, and logical valuesAVERAGEA(value1, value2, ...)AVERAGEA(1,2,3,4, "apple") == 2
AVERAGEIFReturns the average (arithmetic mean) of all the cells in a range that meet a given criteriaAVERAGEIF([criteria_value1, criteria_value2, ...], criteria, [average_value1, average_value2, …])AVERAGEIF([2,4,8,16], ">5", [1, 2, 3, 4]) == 3.5
BASEConverts a number into a text representation with the given radix (base)BASE(number, radix, min_length)BASE(15, 2, 10) == "0000001111"
COSReturns the cosine of a numberCOS(number)COS(12) == 0.844
COSHReturns the hyperbolic cosine of a numberCOSH(number)COSH(12) == 81377.396
COTReturns the contangent of an angleCOT(number)COT(42) == 0.436
COTHReturns the hyperbolic cotangent of an angleCOTH(number)COTH(42) == 1
COUNTCounts how many numbers are in a list of argumentsCOUNT(value1, value2, …)COUNT(12, 14, 16, "apple") == 3
COUNTACounts how many values are in the list of argumentsCOUNTA(value1, value2, …)COUNTA(12, 14, 16, "apple") == 4
COUNTBLANKCounts the number of blank cells within a rangeCOUNTBLANK(value1, value2, …)COUNTBLANK("", 14, 16, "apple") == 1
COUNTIFCounts the number of values within a range that meet the given criteriaCOUNTIF([value1, value2, …], criteria)COUNTIF(["John", "Andrew", "Alice"], "Alice") == 1
COUNTUNIQUECounts the number of unique values within a range of valuesCOUNTUNIQUE(value1, value2, …)COUNTUNIQUE(1,2,2,3,3,3,4) == 4
CSCReturns the cosecant of an angleCSC(number)CSC(12) == -1.8636795977824385
CSCHReturns the hyperbolic cosecant of an angleCSCH(number)CSCH(42) == 1.149904452858712e-18
DELTAReturns 1 if two numbers are equal (0 otherwise)DELTA(value1, value2)DELTA(20,20) == 1
EReturns the value of e (e.g. 2.718..)E()E() == 2.718281828459045
EVENRounds a number up to the nearest even integerEVEN(number)EVEN(3) == 4
EXPReturns e raised to the power of a given numberEXP(number)EXP(4) == 54.598
LARGEReturns the k-th largest value in a data setLARGE([number1, number2, …], 3)LARGE([3,5,3,5,4,4,2,4,6,7], 3) == 5
LNReturns the natural logarithm of a numberLN(number)LN(12) == 2.485
LOGReturns the logarithm of a number to a specified baseLOG(number, base)LOG(12, 10) == 1.079
MAXReturns the maximum value in a list of argumentsMAX(value1, value2, …)MAX(12, 24, 13, 82, 1, 92) == 92
MAXAReturns the maximum value in a list of arguments, including numbers, text, and logical valuesMAXA(value1, value2, …)MAXA(12, 24, 13, 82, 1, 92, "apple") == 92
MEDIANReturns the median of the given numbersMEDIAN(value1, value2, …)MEDIAN(12, 24, 13, 82, 92) == 24
MINReturns the minimum value in a list of argumentsMIN(value1, value2, …)MIN(12, 24, 13, 82, 1, 92) == 1
MINAReturns the smallest value in a list of arguments, including numbers, text, and logical valuesMINA(value1, value2, …)MINA(12, 24, 13, 82, 1, 92, "apple") == 0
MODReturns the remainder from divisionMOD(number, divisor)MOD(12,5) == 2
MODEReturns the most common value in a data setMODE(value1, value2, …)MODE(12, 12, 24, 13, 82) == 12
ODDRounds a number up to the nearest odd integerODD(number)ODD(2) == 3
PIReturns the value of pi (i.e. 3.142...)PI()PI() == 3.142
POWERReturns the result of a number raised to a powerPOWER(number, power)POWER(12,2) == 144
PRODUCTMultiplies its argumentsPRODUCT(value1, value2, …)PRODUCT(4,6,8) == 192
QUOTIENTReturns the integer portion of a divisionQUOTIENT(numerator, denominator)QUOTIENT(62,11) == 5
RANDReturns a random number between 0 and 1RAND()RAND() == 0.213
RANDBETWEENReturns a random number between the numbers you specifyRANDBETWEEN(bottom, top)RANDBETWEEN(12, 18) == 14
ROUNDRounds a number to a specified number of digitsROUND(number, num_digits)ROUND(626.321, 2) == 626.32
ROUNDDOWNRounds a number down, toward zeroROUNDDOWN(number, num_digits)ROUNDDOWN(6.8, 0) == 6
ROUNDUPRounds a number up, away from zeroROUNDUP(number, num_digits)ROUNDUP(6.2, 0) == 7
SECReturns the secant of an angleSEC(number)SEC(180) == -1.671
SECHReturns the hyperbolic secant of an angleSECH(number)SECH(180) == 1.343
SINReturns the sine of the given angleSIN(number)SIN(45) == 0.851
SINHReturns the hyperbolic sine of a numberSINH(number)SINH(3) == 10.018
SMALLReturns the k-th smallest value in a data setSMALL(array, k)SMALL([1,2,3,4,5,6], 3) == 3
SQRTReturns a positive square rootSQRT(number)SQRT(12) == 3.464
SQRTPIReturns the square root of (number * pi)SQRTPI(number)SQRTPI(12) == 6.14
STANDARDIZEReturns a normalized valueSTANDARDIZE(x, mean, standard_dev)STANDARDIZE(42, 40, 1.5) == 1.333
SUMAdds its arguments togetherSUM(number1, number2, …)SUM(10, 20, 11, 14) == 55
SUMIFAdds the cells specified by a given criteriaSUMIF([number1, number2, ...], logical)SUMIF([10, 20, 30, 11, 14], ">15") == 50
SUMSQReturns the sum of the squares of the argumentsSUMSQ(number1, number2, …)SUMSQ(3, 4) == 25
SUMX2MY2Returns the sum of the difference of squares of corresponding values in two arraysSUMX2MY2(array_x, array_y)SUMX2MY2([1,2], [3,4]) == -20
SUMX2PY2Returns the sum of the sum of squares of corresponding values in two arraysSUMX2PY2(array_x, array_y)SUMX2PY2([1,2], [3,4]) == 30
SUMXMY2Returns the sum of squares of differences of corresponding values in two arraysSUMXMY2(array_x, array_y)SUMXMY2([1,2], [3,4]) == 8
TANReturns the tangent of a numberTAN(number)TAN(10) == 0.648
TANHReturns the hyperbolic tangent of a numberTANH(number)TANH(0.2) == 0.197
TRUNCTruncates a number to an integerTRUNC(number, num_digits)TRUNC(12.222, 1) == 12.2

### Text Functions

FunctionDescriptionFormatExample
CLEANRemoves all nonprintable characters from textCLEAN("text")CLEAN("mytext") == "mytext"
CONCATCombines the text from strings into a single stringCONCAT("text1", "text2", …)CONCAT("apple", ", ", "orange") == "apple, orange"
EXACTChecks to see if two text values are identical (case sensitive)EXACT(value1, value2)EXACT("apple", "apple") == true
FINDFinds one text value within another (case-sensitive)FIND("find_text", "within_text", start_num)FIND("M", "Miriam McGovern", 3) == 8
LEFTReturns the leftmost characters from a text valueLEFT(value, num_chars)LEFT("apple", 3) == "app"
LENReturns the number of characters in a text stringLEN(value)LEN("apple") == 5
LOWERConverts text to lowercaseLOWER("text")LOWER("APPLE") == "apple"
MIDReturns a specific number of characters from a text string starting at the position you specifyMID(value, start_num, num_chars)MID("apple", 2, 2) == "pp"
PROPERCapitalizes the first letter in each word of a text valuePROPER("text")PROPER("FIX this Sentence") == "Fix This Sentence"
REPLACEReplaces characters within textREPLACE("old_text", start_number, number_characters, "new_text")REPLACE("abcdefghijk", 6, 5, "") == "abcdek"
REPTRepeats text a given number of timesREPT("text", number_times)REPT("-", 3) == "---"
RIGHTReturns the rightmost characters from a text valueRIGHT(value, num_chars)RIGHT("apple", 3) == "ple"
SEARCHFinds one text value within another (not case-sensitive)SEARCH(find_text, within_text, start_num)SEARCH("orange", "apple & orange", 1) == 9
SUBSTITUTESubstitutes new text for old text in a text stringSUBSTITUTE("text", "old_text", "new_text")SUBSTITUTE("apple", "p", "a") == "aaale"
TRIMRemoves spaces from textTRIM("text")TRIM("apple") == "apple"
UPPERConverts text to uppercaseUPPER("text")UPPER("convert THIS text") == "CONVERT THIS TEXT"
VALUEConverts a text argument to a numberVALUE("text")VALUE("12") == 12