Supported Formula Functions

Amazing Fields includes support for 100+ common functions for use in field formulas.  This includes the most common functions from spreadsheets you are familiar with like Excel and Google Sheets.  This lets you reuse your existing knowledge and equations while applying them to calculations on your cards.  The goal is to make it possible for you do do anything in your cards that you could do in a spreadsheet.

If you see a function missing that you would like to have added, please contact support and let me know.  I am happy to add anything you think would be useful.  

You can use any of these functions in any formula calculation in Amazing Fields.  To learn more about any specific function please take a look at the longer descriptions in the Google Sheets function list or the Formula.js function list. The functions should work the same as they do there.  If you have any questions, send a message to support through the messaging icon on the bottom right of this page.

Note: Date fields do not work the same way as they do in a spreadsheet.  You can not perform math on dates directly or compare them.  Instead you need to use the extra helper functions at the end of this list.  (DATE_ADD, DATE_SUBTRACT, DATE_DIFF).

List Data

Some of the functions take input in the form of a list of data items. When you need to pass data to these you either need to use a "List Field" in a formula or you need to format the data as a list: [1,2,3]

Function Example Result
Logical Functions
AND AND(true, false, true) FALSE
FALSE FALSE() FALSE
IF IF(true, 'Hello!', 'Goodbye!') Hello!
IFS IFS(false, 'Hello!', true, 'Goodbye!') Goodbye!
IFERROR IFERROR('#DIV/0!', 'Error') Error
IFNA IFNA('#N/A', 'Error') Error
NOT NOT(true) FALSE
OR OR(true, false, true) TRUE
SWITCH SWITCH(7, 9, 'Nine', 7, 'Seven')
SWITCH(expression, case1, value1, caseN, valueN, ..., default_value)<br>
        

Note: the default_value is required.

Seven
TRUE TRUE() TRUE
XOR XOR(true, false, true) FALSE
Math Functions
ABS ABS(-4) 4
ACOS ACOS(-0.5) 2.094395102
ACOSH ACOSH(10) 2.993222846
ACOT ACOT(2) 0.463647609
ACOTH ACOTH(6) 0.1682361183
AGGREGATE AGGREGATE(9, 4, [-5,15], [32,'Hello World!']) 10,32
ARABIC ARABIC('MCMXII') 1912
ASIN ASIN(-0.5) -0.5235987756
ASINH ASINH(-2.5) -1.647231146
ATAN ATAN(1) 0.7853981634
ATAN2 ATAN2(-1, -1) -2.35619449
ATANH ATANH(-0.1) -0.1003353477
BASE BASE(15, 2, 10) 1111
CEILING CEILING(-5.5, 2, -1) -6
CEILINGMATH CEILINGMATH(-5.5, 2, -1) -6
CEILINGPRECISE CEILINGPRECISE(-4.1, -2) -4
COMBIN COMBIN(8, 2) 28
COMBINA COMBINA(4, 3) 20
COS COS(1) 0.5403023059
COSH COSH(1) 1.543080635
COT COT(30) -0.1561199522
COTH COTH(2) 1.037314721
CSC CSC(15) 1.537780562
CSCH CSCH(1.5) 0.4696424406
DECIMAL DECIMAL('FF', 16) 255
ERF ERF(1) 0.8427007929
ERFC ERFC(1) 0.1572992071
EVEN EVEN(-1) -2
EXP EXP(1) 2.718281828
FACT FACT(5) 120
FACTDOUBLE FACTDOUBLE(7) 105
FLOOR FLOOR(-3.1) -4
FLOORMATH FLOORMATH(-4.1, -2, -1) -4
FLOORPRECISE FLOORPRECISE(-3.1, -2) -4
GCD GCD(24, 36, 48) 12
INT INT(-8.9) -9
ISEVEN ISEVEN(-2.5) TRUE
ISOCEILING ISOCEILING(-4.1, -2) -4
ISODD ISODD(-2.5) FALSE
LCM LCM(24, 36, 48) 144
LN LN(86) 4.454347296
LOG LOG(8, 2) 3
LOG10 LOG10(100000) 5
MOD MOD(3, -2) -1
MROUND MROUND(-10, -3) -9
MULTINOMIAL MULTINOMIAL(2, 3, 4) 1260
ODD ODD(-1.5) -3
POWER POWER(5, 2) 25
PRODUCT PRODUCT(5, 15, 30) 2250
QUOTIENT QUOTIENT(-10, 3) -3
RADIANS RADIANS(180) 3.141592654
RAND RAND() [Random real number greater between 0 and 1]
RANDBETWEEN RANDBETWEEN(-1, 1) [Random integer between bottom and top]
ROUND ROUND(626.3, -3) 1000
ROUNDDOWN ROUNDDOWN(-3.14159, 2) -3.14
ROUNDUP ROUNDUP(-3.14159, 2) -3.15
SEC SEC(45) 1.903594407
SECH SECH(45) 5.73E-20
SIGN SIGN(-0.00001) -1
SIN SIN(1) 0.8414709848
SINH SINH(1) 1.175201194
SQRT SQRT(16) 4
SQRTPI SQRTPI(2) 2.506628275
SUBTOTAL SUBTOTAL(9, [-5,15], [32,'Hello World!']) 10,32
SUM SUM(-5, 15, 32, 'Hello World!') 42
SUMIF SUMIF([2,4,8,16], '>5') 24
SUMIFS SUMIFS([2,4,8,16], [1,2,3,4], '>=2', [1,2,4,8], '<=4') 12
SUMPRODUCT SUMPRODUCT([[1,2],[3,4]], [[1,0],[0,1]]) 5
SUMSQ SUMSQ(3, 4) 25
SUMX2MY2 SUMX2MY2([1,2], [3,4]) -20
SUMX2PY2 SUMX2PY2([1,2], [3,4]) 30
SUMXMY2 SUMXMY2([1,2], [3,4]) 8
TAN TAN(1) 1.557407725
TANH TANH(-2) -0.9640275801
TRUNC TRUNC(-8.9) -8
Statistical Functions
AVEDEV AVEDEV([2,4], [8,16]) 4.5
AVERAGE AVERAGE([2,4], [8,16]) 7.5
AVERAGEA AVERAGEA([2,4], [8,16]) 7.5
AVERAGEIF AVERAGEIF([2,4,8,16], '>5', [1, 2, 3, 4]) 3.5
AVERAGEIFS AVERAGEIFS([2,4,8,16], [1,2,3,4], '>=2', [1,2,4,8], '<=4') 6
BETADIST BETADIST(2, 8, 10, true, 1, 3) 0.6854705810117458
BETAINV BETAINV(0.6854705810117458, 8, 10, 1, 3) 1.9999999999999998
BINOMDIST BINOMDIST(6, 10, 0.5, false) 0.205078125
CORREL CORREL([3,2,4,5,6], [9,7,12,15,17]) 0.9970544855015815
COUNT COUNT([1,2], [3,4]) 4
COUNTA COUNTA([1, null, 3, 'a', '', 'c']) 4
COUNTBLANK COUNTBLANK([1, null, 3, 'a', '', 'c']) 2
COUNTIF COUNTIF(['Caen', 'Melbourne', 'Palo Alto', 'Singapore'], 'a') 3
COUNTIFS COUNTIFS([2,4,8,16], [1,2,3,4], '>=2', [1,2,4,8], '<=4') 2
COUNTUNIQUE COUNTUNIQUE([1,1,2,2,3,3]) 3
COVARIANCEP COVARIANCEP([3,2,4,5,6], [9,7,12,15,17]) 5.2
COVARIANCES COVARIANCES([2,4,8], [5,11,12]) 9.666666666666668
DEVSQ DEVSQ([2,4,8,16]) 115
EXPONDIST EXPONDIST(0.2, 10, true) 0.8646647167633873
FDIST FDIST(15.2069, 6, 4, false) 0.0012237917087831735
FINV FINV(0.01, 6, 4) 0.10930991412457851
FISHER FISHER(0.75) 0.9729550745276566
FISHERINV FISHERINV(0.9729550745276566) 0.75
FORECAST FORECAST(30, [6,7,9,15,21], [20,28,31,38,40]) 10.607253086419755
FREQUENCY FREQUENCY([79,85,78,85,50,81,95,88,97], [70,79,89]) 1,2,4,2
GAMMA GAMMA(2.5) 1.3293403919101043
GAMMALN GAMMALN(10) 12.801827480081961
GAUSS GAUSS(2) 0.4772498680518208
GEOMEAN GEOMEAN([2,4], [8,16]) 5.656854249492381
GROWTH GROWTH([2,4,8,16], [1,2,3,4], [5]) 32.00000000000003
HARMEAN HARMEAN([2,4], [8,16]) 4.266666666666667
HYPGEOMDIST HYPGEOMDIST(1, 4, 8, 20, false) 0.3632610939112487
INTERCEPT INTERCEPT([2,3,9,1,8], [6,5,11,7,5]) 0.04838709677419217
KURT KURT([3,4,5,2,3,4,5,6,4,7]) -0.15179963720841627
LARGE LARGE([3,5,3,5,4,4,2,4,6,7], 3) 5
LINEST LINEST([1,9,5,7], [0,4,2,3], true, true) 2,1
LOGNORMDIST LOGNORMDIST(4, 3.5, 1.2, true) 0.0390835557068005
LOGNORMINV LOGNORMINV(0.0390835557068005, 3.5, 1.2, true) 4.000000000000001
MAX MAX([0.1,0.2], [0.4,0.8], [true, false]) 0.8
MAXA MAXA([0.1,0.2], [0.4,0.8], [true, false]) 1
MEDIAN MEDIAN([1,2,3], [4,5,6]) 3.5
MIN MIN([0.1,0.2], [0.4,0.8], [true, false]) 0.1
MINA MINA([0.1,0.2], [0.4,0.8], [true, false]) 0
MODEMULT MODEMULT([1,2,3,4,3,2,1,2,3]) 2,3
MODESNGL MODESNGL([1,2,3,4,3,2,1,2,3]) 2
NORMDIST NORMDIST(42, 40, 1.5, true) 0.9087887802741321
NORMINV NORMINV(0.9087887802741321, 40, 1.5) 42
NORMSDIST NORMSDIST(1, true) 0.8413447460685429
NORMSINV NORMSINV(0.8413447460685429) 1.0000000000000002
PEARSON PEARSON([9,7,5,3,1], [10,6,1,5,3]) 0.6993786061802354
PERCENTILEEXC PERCENTILEEXC([1,2,3,4], 0.3) 1.5
PERCENTILEINC PERCENTILEINC([1,2,3,4], 0.3) 1.9
PERCENTRANKEXC PERCENTRANKEXC([1,2,3,4], 2, 2) 0.4
PERCENTRANKINC PERCENTRANKINC([1,2,3,4], 2, 2) 0.33
PERMUT PERMUT(100, 3) 970200
PERMUTATIONA PERMUTATIONA(4, 3) 64
PHI PHI(0.75) 0.30113743215480443
POISSONDIST POISSONDIST(2, 5, true) 0.12465201948308113
PROB PROB([1,2,3,4], [0.1,0.2,0.2,0.1], 2, 3) 0.4
QUARTILEEXC QUARTILEEXC([1,2,3,4], 1) 1.25
QUARTILEINC QUARTILEINC([1,2,3,4], 1) 1.75
RANKAVG RANKAVG(4, [2,4,4,8,8,16], false) 4.5
RANKEQ RANKEQ(4, [2,4,4,8,8,16], false) 4
RSQ RSQ([9,7,5,3,1], [10,6,1,5,3]) 0.4891304347826088
SKEW SKEW([3,4,5,2,3,4,5,6,4,7]) 0.3595430714067974
SKEWP SKEWP([3,4,5,2,3,4,5,6,4,7]) 0.303193339354144
SLOPE SLOPE([1,9,5,7], [0,4,2,3]) 2
SMALL SMALL([3,5,3,5,4,4,2,4,6,7], 3) 3
STANDARDIZE STANDARDIZE(42, 40, 1.5) 1.3333333333333333
STDEVA STDEVA([2,4], [8,16], [true, false]) 6.013872850889572
STDEVP STDEVP([2,4], [8,16], [true, false]) 5.361902647381804
STDEVPA STDEVPA([2,4], [8,16], [true, false]) 5.489889697333535
STDEVS STDEVS([2,4], [8,16], [true, false]) 6.191391873668904
STEYX STEYX([2,3,9,1,8,7,5], [6,5,11,7,5,4,4]) 3.305718950210041
TDIST TDIST(60, 1, true) 0.9946953263673741
TINV TINV(0.9946953263673741, 1) 59.99999999996535
TRIMMEAN TRIMMEAN([4,5,6,7,2,3,4,5,1,2,3], 0.2) 3.7777777777777777
VARA VARA([2,4], [8,16], [true, false]) 36.16666666666667
VARP VARP([2,4], [8,16], [true, false]) 28.75
VARPA VARPA([2,4], [8,16], [true, false]) 30.13888888888889
VARS VARS([2,4], [8,16], [true, false]) 38.333333333333336
WEIBULLDIST WEIBULLDIST(105, 20, 100, true) 0.9295813900692769
ZTEST ZTEST([3,6,7,8,6,5,4,2,1,9], 4) 0.09057419685136381
Financial Functions
ACCRINT ACCRINT('01/01/2011', '02/01/2011', '07/01/2014', 0.1, 1000, 1, 0) 350
CUMIPMT CUMIPMT(0.1/12, 30*12, 100000, 13, 24, 0) -9916.772514
CUMPRINC CUMPRINC(0.1/12, 30*12, 100000, 13, 24, 0) -614.0863271
DB DB(1000000, 100000, 6, 1, 6) 159500
DDB DDB(1000000, 100000, 6, 1, 1.5) 250000
DOLLARDE DOLLARDE(1.1, 16) 1.625
DOLLARFR DOLLARFR(1.625, 16) 1.1
EFFECT EFFECT(0.1, 4) 0.1038128906
FV FV(0.1/12, 10, -100, -1000, 0) 2124.874409
FVSCHEDULE FVSCHEDULE(100, [0.09,0.1,0.11]) 133.089
IPMT IPMT(0.1/12, 6, 2*12, 100000, 1000000, 0) 928.8235718
IRR IRR([-75000,12000,15000,18000,21000,24000], 0.075) 0.05715142887
ISPMT ISPMT(0.1/12, 6, 2*12, 100000) -625
MIRR MIRR([-75000,12000,15000,18000,21000,24000], 0.1, 0.12) 0.07971710361
NOMINAL NOMINAL(0.1, 4) 0.09645475634
NPER NPER(0.1/12, -100, -1000, 10000, 0) 63.39385423
NPV NPV(0.1, -10000, 2000, 4000, 8000) 1031.350318
PDURATION PDURATION(0.1, 1000, 2000) 7.272540897
PMT PMT(0.1/12, 2*12, 100000, 1000000, 0) -42426.08564
PPMT PPMT(0.1/12, 6, 2*12, 100000, 1000000, 0) -43354.90921
PV PV(0.1/12, 2*12, 1000, 10000, 0) -29864.95026
RATE RATE(2*12, -1000, -10000, 100000, 0, 0.1) 0.06517891177
Engineering Functions
BIN2DEC BIN2DEC(101010) 42
BIN2HEX BIN2HEX(101010) 2a
BIN2OCT BIN2OCT(101010) 52
BITAND BITAND(42, 24) 8
BITLSHIFT BITLSHIFT(42, 24) 704643072
BITOR BITOR(42, 24) 58
BITRSHIFT BITRSHIFT(42, 2) 10
BITXOR BITXOR(42, 24) 50
COMPLEX COMPLEX(3, 4) 3+4i
CONVERT CONVERT(64, 'kibyte', 'bit') 524288
DEC2BIN DEC2BIN(42) 101010
DEC2HEX DEC2HEX(42) 2a
DEC2OCT DEC2OCT(42) 52
DELTA DELTA(42, 42) 1
ERF ERF(1) 0.8427007929497149
ERFC ERFC(1) 0.1572992070502851
GESTEP GESTEP(42, 24) 1
HEX2BIN HEX2BIN('2a') 101010
HEX2DEC HEX2DEC('2a') 42
HEX2OCT HEX2OCT('2a') 52
IMABS IMABS('3+4i') 5
IMAGINARY IMAGINARY('3+4i') 4
IMARGUMENT IMARGUMENT('3+4i') 0.9272952180016122
IMCONJUGATE IMCONJUGATE('3+4i') 3-4i
IMCOS IMCOS('1+i') 0.8337300251311491-0.9888977057628651i
IMCOSH IMCOSH('1+i') 0.8337300251311491+0.9888977057628651i
IMCOT IMCOT('1+i') 0.21762156185440265-0.8680141428959249i
IMCSC IMCSC('1+i') 0.6215180171704283-0.3039310016284264i
IMCSCH IMCSCH('1+i') 0.3039310016284264-0.6215180171704283i
IMDIV IMDIV('1+2i', '3+4i') 0.44+0.08i
IMEXP IMEXP('1+i') 1.4686939399158851+2.2873552871788423i
IMLN IMLN('1+i') 0.3465735902799727+0.7853981633974483i
IMLOG10 IMLOG10('1+i') 0.1505149978319906+0.3410940884604603i
IMLOG2 IMLOG2('1+i') 0.5000000000000001+1.1330900354567985i
IMPOWER IMPOWER('1+i', 2) 1.2246063538223775e-16+2.0000000000000004i
IMPRODUCT IMPRODUCT('1+2i', '3+4i', '5+6i') -85+20i
IMREAL IMREAL('3+4i') 3
IMSEC IMSEC('1+i') 0.4983370305551868+0.591083841721045i
IMSECH IMSECH('1+i') 0.4983370305551868-0.591083841721045i
IMSIN IMSIN('1+i') 1.2984575814159773+0.6349639147847361i
IMSINH IMSINH('1+i') 0.6349639147847361+1.2984575814159773i
IMSQRT IMSQRT('1+i') 1.0986841134678098+0.45508986056222733i
IMSUB IMSUB('3+4i', '1+2i') 2+2i
IMSUM IMSUM('1+2i', '3+4i', '5+6i') 9+12i
IMTAN IMTAN('1+i') 0.2717525853195117+1.0839233273386946i
OCT2BIN OCT2BIN('52') 101010
OCT2DEC OCT2DEC('52') 42
OCT2HEX OCT2HEX('52') 2a
Text Functions
CHAR CHAR(65) A
CLEAN CLEAN('Monthly report') Monthly report
CODE CODE('A') 65
CONCATENATE CONCATENATE('Andreas', ' ', 'Hauser') Andreas Hauser
EXACT EXACT('Word', 'word') FALSE
FIND FIND('M', 'Miriam McGovern', 3) 8
LEFT LEFT('Sale Price', 4) Sale
LEN LEN('Phoenix, AZ') 11
LOWER LOWER('E. E. Cummings') e. e. cummings
MID MID('Fluid Flow', 7, 20) Flow
NUMBERVALUE NUMBERVALUE('2.500,27', ',', '.') 2500.27
PROPER PROPER('this is a TITLE') This Is A Title
REPLACE REPLACE('abcdefghijk', 6, 5, '*') abcde*k
REPT REPT('*-', 3) *-*-*-
RIGHT RIGHT('Sale Price', 5) Price
ROMAN ROMAN(499) CDXCIX
SEARCH SEARCH('margin', 'Profit Margin') 8
SUBSTITUTE SUBSTITUTE('Quarter 1, 2011', '1', '2', 3) Quarter 1, 2012
T T('Rainfall') Rainfall
TEXTJOIN TEXTJOIN(' ', true, 'The', '', 'sun', 'will', 'come', 'up', 'tomorrow.') The sun will come up tomorrow.
TRIM TRIM(' First Quarter Earnings ') First Quarter Earnings
UNICHAR UNICHAR(66) B
UNICODE UNICODE('B') 66
UPPER UPPER('total') TOTAL
Date Functions (beta support)
DATE DATE(2008, 7, 8) Tue Jul 08 2008 00:00:00 GMT-0700 (PDT)
DATEDIF DATEDIF("7/16/1969", "7/24/1969", "Y") 0
DATEVALUE DATEVALUE('8/22/2011') Mon Aug 22 2011 00:00:00 GMT-0700 (PDT)
DAY DAY('15-Apr-11') 15
DAYS DAYS('3/15/11', '2/1/11') 42
DAYS360 DAYS360('1-Jan-11', '31-Dec-11') 360
EDATE EDATE('1/15/11', -1) Wed Dec 15 2010 00:00:00 GMT-0800 (PST)
EOMONTH EOMONTH('1/1/11', -3) Sun Oct 31 2010 00:00:00 GMT-0700 (PDT)
HOUR HOUR('7/18/2011 7:45:00 AM') 7
MINUTE MINUTE('2/1/2011 12:45:00 PM') 45
ISOWEEKNUM ISOWEEKNUM('3/9/2012') 10
MONTH MONTH('15-Apr-11') 4
NETWORKDAYS NETWORKDAYS('10/1/2012', '3/1/2013', ['11/22/2012']) 109
NETWORKDAYSINTL NETWORKDAYSINTL('1/1/2006', '2/1/2006', 7, ['1/2/2006']) 23
NOW NOW()
Thu Feb 20 2020 23:02:55 GMT+0100 (Central European Standard Time)
SECOND SECOND('2/1/2011 4:48:18 PM') 18
TIME TIME(16, 48, 10) 0.7001157407
TIMEVALUE TIMEVALUE('22-Aug-2011 6:35 AM') 0.2743055556
TODAY TODAY()
Thu Feb 20 2020 23:02:55 GMT+0100 (Central European Standard Time)
WEEKDAY WEEKDAY('2/14/2008', 3) 3
YEAR YEAR('7/5/2008') 2008
WEEKNUM WEEKNUM('3/9/2012', 2) 11
WORKDAY WORKDAY('10/1/2008', 151, ['11/26/2008', '12/4/2008']) Mon May 04 2009 00:00:00 GMT-0700 (PDT)
WORKDAYINTL WORKDAYINTL('1/1/2012', 30, 17) Sun Feb 05 2012 00:00:00 GMT-0800 (PST)
YEARFRAC YEARFRAC('1/1/2012', '7/30/2012', 3) 0.5780821918
Date Field Functions
DATE_ADD DATE_ADD('1/1/2012', 3, 'day')
Add a whole number of time units to the given date.
supported units: day, week, month, year, hour, minute, second
DATE_SUBTRACT DATE_SUBTRACT('1/1/2012', 3, 'day')
Subtract a whole number of time units from the given date.
supported units: day, week, month, year, hour, minute, second
DATE_DIFF DATE_DIFF('1/1/2012', '1/8/2012', 'hour')
Compute the difference between the dates in the given units.
Note: the value returned is the number of whole time units rounded down.

DATE_DIFF('1/1/2012', '1/8/2012', 'hour', true)
Adding true as the last parameter will return the number in floating point instead of rounding to whole units.
supported units: day, week, month, year, hour, minute, second

DATE_STARTOF DATE_STARTOF('1/1/2012', 'month')
Return the start of the time range of the given unit.
supported units: day, week, month, year, hour, minute, second
DATE_ENDOF DATE_ENDOF('1/1/2012', 'month')
Return the end of the time range of the given unit.
supported units: day, week, month, year, hour, minute, second
Previously Supported Functions
REGEXEXTRACT
REGEXMATCH
REGEXREPLACE
SPLIT

NOTE: Contact support@amazingpowerups.com if you need a workaround for these being removed.
Did this answer your question? Thanks for the feedback There was a problem submitting your feedback. Please try again later.

Still need help? Contact Us Contact Us