Formula Functions
A list of all the formulas supported in calculated fields.
The formulas work like Excel and Google Sheets, and examples you find on the Internet will generally work in AllDrafts' calculated fields with little or no modification as lon gas you use field names instead of row-column references. (AllDrafts has no sense of cell D23, but may have a field named Interest Rate, which you can refer to in a formula by using the underscore (_) character for spaces, like Interest_Rate.)
Ultimately AllDrafts renders everything as text, but if a formula results in a number or date, AllDrafts will use the field's formatting property to render it.
ALLDRAFTS-ONLY FUNCTIONS
ARTICLEFOR("certificate")
a
CAPITALIZE
CAPITALIZE("certificate")
Certificate
IFSINGULAR
IFSINGULAR("employees", "one", "multiple")
multiple
PLURAL
PLURAL("certificate", 2)
certificates
PLURALPOSSESSIVE
PLURALPOSSESSIVE("owner", 2)
owners'
PRONOUNFOR
PRONOUNFOR("John Doe", "subjective")
he
TITLE
TITLE()
in a document titled Employment Agreement
Employment Agreement
=WORKDAY(Start_Date, 30, USHOLIDAYS())
The work day 30 days after Start_Date, excluding weekends and US holidays.
DATE
DATE
DATE(2008, 7, 8)
Tue Jul 08 2008 00:00:00 GMT-0700 (PDT)
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.7001157407407408
TIMEVALUE
TIMEVALUE("22-Aug-2011 6:35 AM")
0.2743055555555556
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.5780821917808219
FINANCIAL
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.77251395708
CUMPRINC
CUMPRINC(0.1/12, 30*12, 100000, 13, 24, 0)
-614.0863271085149
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.10381289062499977
FV
FV(0.1/12, 10, -100, -1000, 0)
2124.874409194097
FVSCHEDULE
FVSCHEDULE(100, {0.09,0.1,0.11})
133.08900000000003
IPMT
IPMT(0.1/12, 6, 2*12, 100000, 1000000, 0)
928.8235718400465
IRR
IRR({-75000,12000,15000,18000,21000,24000}, 0.075)
0.05715142887178447
ISPMT
ISPMT(0.1/12, 6, 2*12, 100000)
-625
MIRR
MIRR({-75000,12000,15000,18000,21000,24000}, 0.1, 0.12)
0.07971710360838036
NOMINAL
NOMINAL(0.1, 4)
0.09645475633778045
NPER
NPER(0.1/12, -100, -1000, 10000, 0)
63.39385422740764
NPV
NPV(0.1, -10000, 2000, 4000, 8000)
1031.3503176012546
PDURATION
PDURATION(0.1, 1000, 2000)
7.272540897341714
PMT
PMT(0.1/12, 2*12, 100000, 1000000, 0)
-42426.08563793503
PPMT
PPMT(0.1/12, 6, 2*12, 100000, 1000000, 0)
-43354.909209775076
PV
PV(0.1/12, 2*12, 1000, 10000, 0)
-29864.950264779152
RATE
RATE(2*12, -1000, -10000, 100000, 0, 0.1)
0.06517891177181533
ENGINEERING
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
LOGICAL
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
CHOOSE
CHOOSE(2, "red", "blue", "green")
blue
MATH
ABS
ABS(-4)
4
ACOS
ACOS(-0.5)
2.0943951023931957
ACOSH
ACOSH(10)
2.993222846126381
ACOT
ACOT(2)
0.46364760900080615
ACOTH
ACOTH(6)
0.16823611831060645
AGGREGATE
AGGREGATE(9, 4, {-5,15}, {32,"Hello World!"})
10,32
ARABIC
ARABIC("MCMXII")
1912
ASIN
ASIN(-0.5)
-0.5235987755982988
ASINH
ASINH(-2.5)
-1.6472311463710965
ATAN
ATAN(1)
0.7853981633974483
ATAN2
ATAN2(-1, -1)
-2.356194490192345
ATANH
ATANH(-0.1)
-0.10033534773107562
BASE
BASE(15, 2, 10)
0000001111
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.5403023058681398
COSH
COSH(1)
1.5430806348152437
COT
COT(30)
-0.15611995216165922
COTH
COTH(2)
1.0373147207275482
CSC
CSC(15)
1.5377805615408537
CSCH
CSCH(1.5)
0.46964244059522464
DECIMAL
DECIMAL("FF", 16)
255
ERF
ERF(1)
0.8427007929497149
ERFC
ERFC(1)
0.1572992070502851
EVEN
EVEN(-1)
-2
EXP
EXP(1)
2.718281828459045
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.454347296253507
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.141592653589793
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.9035944074044246
SECH
SECH(45)
5.725037161098787e-20
SIGN
SIGN(-0.00001)
-1
SIN
SIN(1)
0.8414709848078965
SINH
SINH(1)
1.1752011936438014
SQRT
SQRT(16)
4
SQRTPI
SQRTPI(2)
2.5066282746310002
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.5574077246549023
TANH
TANH(-2)
-0.9640275800758168
TRUNC
TRUNC(-8.9)
-8
STATISTICAL
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
TEXT
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
REGEXEXTRACT
REGEXEXTRACT("Palo Alto", "Alto")
Alto
REGEXMATCH
REGEXMATCH("Palo Alto", "Alto")
true
REGEXREPLACE
REGEXREPLACE("Sutoiku", "utoiku", "TOIC")
STOIC
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
SPLIT
SPLIT("A,B,C", ",")
A,B,C
SUBSTITUTE
SUBSTITUTE("Quarter 1, 2011", "1", "2", 3)
Quarter 1, 2012
T
T("Rainfall")
Rainfall
TRIM
TRIM(" First Quarter Earnings ")
First Quarter Earnings
UNICHAR
UNICHAR(66)
B
UNICODE
UNICODE("B")
66
UPPER
UPPER("total")
TOTAL
Last updated