Supported Formula Functions

Field Formulas are available to supports only. If you're on the free plan, you'll need to upgrade to use formula functions on your cards.

This article is your complete reference for all formula functions supported in Amazing Fields. Use it whenever you need to find the right function for a calculation, check the correct syntax, or understand how functions behave on your Trello cards.

Amazing Fields supports 100+ functions that work just like their equivalents in Excel and Google Sheets. If you've built spreadsheet formulas before, you can apply that knowledge directly to your cards.

Before you start

A few important things to know before building formulas:

For list data, some functions accept a list as input. You can either reference a List Field in your formula, or format the data manually as [1,2,3] .
For deeper detail on any function, refer to the Google Sheets function list, the functions behave the same way.

Function reference

Functions are grouped by category below. Each row shows the function name, an example call, and the expected result.

Logical functions

Function	Example	Result
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')	Seven
TRUE	TRUE()	TRUE
XOR	XOR(true, false, true)	FALSE

Note on SWITCH: The full syntax is SWITCH(expression, case1, value1, ..., default_value). A default value is always required.

Math functions

Function	Example	Result
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

Function	Example	Result
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

Function	Example	Result
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

Function	Example	Result
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

Function	Example	Result
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)

Standard spreadsheet date functions are supported with beta-level coverage. These functions accept date strings as input.

Function	Example	Result
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

These are custom helper functions built specifically for Amazing Fields. Use these whenever you need to perform date arithmetic. See how to use dates field functions.

Previously supported functions (Removed)

The following functions are no longer supported in Amazing Fields:

REGEXEXTRACT
REGEXMATCH
REGEXREPLACE
SPLIT

Note: If you relied on any of these functions, contact support@amazingpowerups.com for help with workaround options.