Formula function reference
ERP•AI formulas support a large library of functions. Most work the same way as the spreadsheet functions you already know (Excel, Google Sheets), plus a few that are specific to ERP•AI. In the app, click ƒx Functions in any formula editor to search the full list, with an example and result for each function.
Logic
| Function | What it does | Example |
|---|---|---|
IF(condition, if_true, if_false) | Returns one value if the condition is true, another if it’s false | IF(Amount > 1000, "Large", "Small") |
IFS(cond1, val1, cond2, val2, …) | Checks several conditions and returns the value for the first true one | IFS(Score>=90,"A",Score>=75,"B",TRUE,"C") |
SWITCH(expr, case1, val1, …, default) | Matches a value against cases | SWITCH(Priority,"P1","Urgent","P2","High","Normal") |
AND(a, b, …) / OR(a, b, …) / XOR(…) / NOT(a) | Combine or reverse conditions | AND(Paid, Delivered) |
IFERROR(value, fallback) / IFNA(value, fallback) | Returns a fallback if there’s an error | IFERROR(Revenue/Units, 0) |
TRUE() / FALSE() | Boolean constants |
Math and rounding
| Function | What it does |
|---|---|
SUM, PRODUCT, SUMPRODUCT, SUMSQ | Add or multiply values |
SUMIF, SUMIFS | Add values that meet one or more conditions |
ROUND(x, digits), ROUNDUP, ROUNDDOWN, MROUND, TRUNC, INT | Rounding |
CEILING, FLOOR (+ …MATH, …PRECISE variants) | Round up or down to a multiple |
ABS, SIGN, MOD, QUOTIENT, POWER, SQRT, EXP, LN, LOG, LOG10 | Arithmetic |
MIN, MAX, AVERAGE, MEDIAN, LARGE, SMALL | Summary statistics |
COUNT, COUNTA, COUNTBLANK, COUNTIF, COUNTIFS, COUNTUNIQUE | Counting |
RAND, RANDBETWEEN | Random numbers |
AGGREGATE | Apply an aggregate (sum, average…) with options |
Text
| Function | What it does | Example |
|---|---|---|
CONCATENATE(a, b, …) | Joins text | CONCATENATE(First Name," ",Last Name) |
TEXTJOIN(delimiter, ignore_empty, …) | Joins text with a separator | TEXTJOIN(", ", TRUE, City, State) |
LEFT, RIGHT, MID | Take part of a text value | LEFT(Code, 3) |
LEN | Length of a text value | |
UPPER, LOWER, PROPER | Change the case | PROPER(Name) |
TRIM, CLEAN | Remove extra spaces or non-printable characters | |
FIND, SEARCH | Position of text inside other text | |
SUBSTITUTE, REPLACE | Replace text | SUBSTITUTE(Phone,"-","") |
SPLIT | Split text by a separator | |
REPT, EXACT, CHAR, CODE, UNICHAR, UNICODE | Other text tools | |
REGEXMATCH, REGEXEXTRACT, REGEXREPLACE | Pattern matching with regular expressions | REGEXMATCH(Email, "@acme\\.com$") |
NUMBERVALUE, DECIMAL, ROMAN, ARABIC | Convert between text and numbers |
Date and time
| Function | What it does | Example |
|---|---|---|
TODAY(), NOW() | Current date / date and time | |
ADD_DAYS(date, n, unit)(ERP•AI) | Adds days, months, or years to a date. Unit is "D", "M", or "Y" (not case-sensitive) | ADD_DAYS('2025-01-01', 10, 'D') → 2025-01-11 |
DATE(y, m, d), TIME(h, m, s) | Build a date or time | |
DAY, MONTH, YEAR, HOUR, MINUTE, SECOND, WEEKDAY, WEEKNUM, ISOWEEKNUM | Take parts of a date | |
DAYS(end, start), DATEDIF, DAYS360, YEARFRAC | Differences between dates | |
EDATE, EOMONTH | Shift by months / end of month | |
NETWORKDAYS, WORKDAY (+ .INTL variants) | Working-day calculations | |
DATEVALUE, TIMEVALUE | Convert text to a date or time |
Finance
| Function | What it does |
|---|---|
EXCHANGE_RATE(…)(ERP•AI) | Returns the exchange rate for a target currency code (for example USD, SGD). Used with multi-currency apps |
CONVERT_CURRENCY(amount, currency, date)(ERP•AI) | Converts an amount into another currency (for example INR or USD) at the exchange rate for a given date. Used with multi-currency apps |
PMT, IPMT, PPMT, CUMIPMT, CUMPRINC | Loan payments and interest |
PV, FV, FVSCHEDULE, NPV, IRR, MIRR, RATE, NPER, PDURATION | Time value of money |
DB, DDB | Depreciation |
EFFECT, NOMINAL, ACCRINT, DOLLARDE, DOLLARFR | Interest and pricing |
Statistics
AVEDEV, AVERAGEA, AVERAGEIF(S), CORREL, COVARIANCEP/S, DEVSQ, FORECAST, FREQUENCY, GEOMEAN, GROWTH, HARMEAN, INTERCEPT, KURT, LINEST, MODEMULT, MODESNGL, PEARSON, PERCENTILE…, PERCENTRANK…, QUARTILE…, RANKAVG, RANKEQ, RSQ, SKEW, SLOPE, STANDARDIZE, STDEV…, STEYX, TRIMMEAN, VAR…, and distribution functions (NORMDIST, BINOMDIST, POISSONDIST, TDIST, WEIBULLDIST, and more).
Engineering and trigonometry
SIN, COS, TAN (and inverse/hyperbolic variants), RADIANS, SQRTPI, number-base conversions (BIN2DEC, DEC2HEX, HEX2OCT…), bitwise functions (BITAND, BITOR, BITXOR, shifts), complex numbers (COMPLEX, IM…), CONVERT, DELTA, GESTEP, ERF, ERFC, GAMMA, FACT, COMBIN, PERMUT.
Complete alphabetical index
ABS · ACCRINT · ACOS · ACOSH · ACOT · ACOTH · ADD_DAYS · AGGREGATE · AND · ARABIC · ASIN · ASINH · ATAN · ATAN2 · ATANH · AVEDEV · AVERAGE · AVERAGEA · AVERAGEIF · AVERAGEIFS · BETADIST · BETAINV · BIN2DEC · BIN2HEX · BIN2OCT · BINOMDIST · BITAND · BITLSHIFT · BITOR · BITRSHIFT · BITXOR · CEILING · CEILINGMATH · CEILINGPRECISE · CHAR · CLEAN · CODE · COMBIN · COMBINA · COMPLEX · CONCATENATE · CONVERT · CONVERT_CURRENCY · CORREL · COS · COSH · COT · COTH · COUNT · COUNTA · COUNTBLANK · COUNTIF · COUNTIFS · COUNTUNIQUE · COVARIANCEP · COVARIANCES · CSC · CSCH · CUMIPMT · CUMPRINC · DATE · DATEDIF · DATEVALUE · DAY · DAYS · DAYS360 · DB · DDB · DEC2BIN · DEC2HEX · DEC2OCT · DECIMAL · DELTA · DEVSQ · DOLLARDE · DOLLARFR · EDATE · EFFECT · EOMONTH · ERF · ERFC · EVEN · EXACT · EXCHANGE_RATE · EXP · EXPONDIST · FACT · FACTDOUBLE · FALSE · FDIST · FIND · FINV · FISHER · FISHERINV · FLOOR · FLOORMATH · FLOORPRECISE · FORECAST · FREQUENCY · FV · FVSCHEDULE · GAMMA · GAMMALN · GAUSS · GCD · GEOMEAN · GESTEP · GROWTH · HARMEAN · HEX2BIN · HEX2DEC · HEX2OCT · HOUR · HYPGEOMDIST · IF · IFERROR · IFNA · IFS · IMABS · IMAGINARY · IMARGUMENT · IMCONJUGATE · IMCOS · IMCOSH · IMCOT · IMCSC · IMCSCH · IMDIV · IMEXP · IMLN · IMLOG10 · IMLOG2 · IMPOWER · IMPRODUCT · IMREAL · IMSEC · IMSECH · IMSIN · IMSINH · IMSQRT · IMSUB · IMSUM · IMTAN · INT · INTERCEPT · IPMT · IRR · ISOCEILING · ISOWEEKNUM · ISPMT · KURT · LARGE · LCM · LEFT · LEN · LINEST · LN · LOG · LOG10 · LOGNORMDIST · LOGNORMINV · LOWER · MAX · MAXA · MEDIAN · MID · MIN · MINA · MINUTE · MIRR · MOD · MODEMULT · MODESNGL · MONTH · MROUND · MULTINOMIAL · NETWORKDAYS · NETWORKDAYSINTL · NOMINAL · NORMDIST · NORMINV · NORMSDIST · NORMSINV · NOT · NOW · NPER · NPV · NUMBERVALUE · OCT2BIN · OCT2DEC · OCT2HEX · ODD · OR · PDURATION · PEARSON · PERCENTILEEXC · PERCENTILEINC · PERCENTRANKEXC · PERCENTRANKINC · PERMUT · PERMUTATIONA · PHI · PMT · POISSONDIST · POWER · PPMT · PROB · PRODUCT · PROPER · PV · QUARTILEEXC · QUARTILEINC · QUOTIENT · RADIANS · RAND · RANDBETWEEN · RANKAVG · RANKEQ · RATE · REGEXEXTRACT · REGEXMATCH · REGEXREPLACE · REPLACE · REPT · RIGHT · ROMAN · ROUND · ROUNDDOWN · ROUNDUP · RSQ · SEARCH · SEC · SECH · SECOND · SIGN · SIN · SINH · SKEW · SKEWP · SLOPE · SMALL · SPLIT · SQRT · SQRTPI · STANDARDIZE · STDEVA · STDEVP · STDEVPA · STDEVS · STEYX · SUBSTITUTE · SUBTOTAL · SUM · SUMIF · SUMIFS · SUMPRODUCT · SUMSQ · SUMX2MY2 · SUMX2PY2 · SUMXMY2 · SWITCH · T · TAN · TANH · TDIST · TEXTJOIN · TIME · TIMEVALUE · TINV · TODAY · TRIM · TRIMMEAN · TRUE · TRUNC · UNICHAR · UNICODE · UPPER · VARA · VARP · VARPA · VARS · WEEKDAY · WEEKNUM · WEIBULLDIST · WORKDAY · WORKDAYINTL · XOR · YEAR · YEARFRAC · ZTEST