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