🔴 On2Air Forms has been shutdown to focus on our On2Air Backups Airtable app Learn more about automated Airtable backups - on2air.com
You can use On2Air Formulas to live calculate data on a form, like adding quantities, calculating totals, and more.
The Airtable Formulas and Rollups you currently have in your base won’t update live on a form due to Airtable API limitations, so you will need to substitute your Airtable formula with an On2Air Formula to calculate your data.
Here are some common calculations you can use that are a similar replacement for Airtable Formulas. They are based on EXCEL formulas.
- MATH FORMULAS
- LOGICAL FORMULAS
- TEXT FORMULAS
- DATE FORMULAS
- FINANCIAL FORMULAS
- STATISTICAL FORMULAS
- ROLLUP FORMULAS
To see the entire list of formulas available - Formula.js To see a more in-depth description of each formula - Excel Formula List
MATH FORMULAS
| Formula | Description | Example | Result |
|---|---|---|---|
* | Multiply two numeric values | {{Hours}}*{{Hourly Rate}} | |
+ | Add together two numeric values | {{Rate}} + 2 | |
- | Subtract two numeric values | {{Discount}} - 3.00 | |
/ | Divide two numeric values | {{Discount}} / {{No. of Products}} | |
PRODUCT | Multiply numeric values | PRODUCT(5, 15, 30) | 2250 |
ROUND | Rounds a number to a specified number of digits | ROUND(626.3, -3) | 1000 |
ROUNDDOWN | Rounds a number down, toward zero | ROUNDDOWN(-3.14159, 2) | -3.14 |
ROUNDUP | Rounds a number up, away from zero | ROUNDUP(-3.14159, 2) | -3.15 |
SUM | Adds the cells specified by a given criteria | SUM(-5, 15, 32, 'Hello World!') | 42 |
SUMIFS | Adds the cells in a range that meet multiple criteria | SUMIFS([2,4,8,16], [1,2,3,4], '>=2', [1,2,4,8], '<=4') | 12 |
LOGICAL FORMULAS
| Formula | Description | Example | Result |
|---|---|---|---|
NOT | Reverses the logic of its argument | NOT(true) | false |
SWITCH | Evaluates an expression against a list of values and returns the result corresponding to the first matching value. If there is no match, an optional default value may be returned. | SWITCH(7, 9, 'Nine', 7, 'Seven') | Seven |
IFNA | Returns the value you specify if the expression resolves to #N/A, otherwise returns the result of the expression | IFNA('#N/A', 'Error') | Error |
IFS | Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition. | IFS(false, 'Hello!', true, 'Goodbye!') | Goodbye! |
OR | Returns TRUE if any argument is TRUE | OR(true, false, true) | true |
true | Returns the logical value TRUE | TRUE() | true |
IFERROR | Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula | IFERROR('#DIV/0!', 'Error') | Error |
IF | Specifies a logical test to perform | IF(true, 'Hello!', 'Goodbye!') | Hello! |
AND | Returns TRUE if all of its arguments are TRUE | AND(true, false, true) | false |
false | Returns the logical value FALSE | FALSE() | false |
TEXT FORMULAS
| Formula | Description | Example | Result |
|---|---|---|---|
CLEAN | Removes all nonprintable characters from text | CLEAN('Monthly report') | Monthly report |
CODE | Returns a numeric code for the first character in a text string | CODE('A') | 65 |
CONCATENATE | Join values together | CONCATENATE({{First Name}}, ' ', {{Last Name}}) | Andreas Hauser |
EXACT | Checks to see if two text values are identical | EXACT('Word', 'word') | false |
FIND | locate one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string. | FIND('M', 'Miriam McGovern', 3) | 8 |
LEFT | Returns the leftmost characters from a text value | LEFT('Sale Price', 4) | Sale |
LEN | Returns the number of characters in a text string | LEN('Phoenix, AZ') | 11 |
LOWER | Converts text to lowercase | LOWER('E. E. Cummings') | e. e. cummings |
MID | Returns a specific number of characters from a text string starting at the position you specify | MID('Fluid Flow', 7, 20) | Flow |
PROPER | Capitalizes the first letter in each word of a text value | 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 | Replaces characters within text | REPLACE('abcdefghijk', 6, 5, '*') | abcde*k |
REPT | Repeats text a given number of times | REPT('*-', 3) | *-*-*- |
RIGHT | Returns the rightmost characters from a text value | RIGHT('Sale Price', 5) | Price |
SPLIT | SPLIT('A,B,C', ',') | A,B,C | |
SUBSTITUTE | Substitutes new text for old text in a text string | SUBSTITUTE('Quarter 1, 2011', '1', '2', 3) | Quarter 1, 2012 |
UNICODE | Returns the number (code point) that corresponds to the first character of the text | UNICODE('B') | 66 |
UPPER | Converts text to uppercase | UPPER('total') | TOTAL |
DATE FORMULAS
| Formula | Description | Example | Result |
|---|---|---|---|
DATE | Returns the serial number of a particular date | DATE(2008, 7, 8) | Tue Jul 08 2008 00:00:00 GMT-0700 (PDT) |
DATEVALUE | Converts a date in the form of text to a serial number | DATEVALUE('8/22/2011') | Mon Aug 22 2011 00:00:00 GMT-0700 (PDT) |
DAY | Converts a serial number to a day of the month | DAY('15-Apr-11') | 15 |
DAYS | Returns the number of days between two dates | DAYS('3/15/11', '2/1/11') | 42 |
HOUR | Converts a serial number to an hour | HOUR('7/18/2011 7:45:00 AM') | 7 |
MINUTE | Converts a serial number to a minute | MINUTE('2/1/2011 12:45:00 PM') | 45 |
ISOWEEKNUM | Returns the number of the ISO week number of the year for a given date | ISOWEEKNUM('3/9/2012') | 10 |
MONTH | Converts a serial number to a month | MONTH('15-Apr-11') | 4 |
NOW | Returns the serial number of the current date and time | NOW() | Thu Feb 20 2020 23:02:55 GMT+0100 (Central European Standard Time) |
SECOND | Converts a serial number to a second | SECOND('2/1/2011 4:48:18 PM') | 18 |
NOW | Returns the serial number of the current date and time | NOW() | Thu Feb 20 2020 23:02:55 GMT+0100 (Central European Standard Time) |
SECOND | Converts a serial number to a second | SECOND('2/1/2011 4:48:18 PM') | 18 |
TODAY | Returns the serial number of today's date | TODAY() | Thu Feb 20 2020 23:02:55 GMT+0100 (Central European Standard Time) |
WEEKDAY | Converts a serial number to a day of the week | WEEKDAY('2/14/2008', 3) | 3 |
YEAR | Converts a serial number to a year | YEAR('7/5/2008') | 2008 |
WEEKNUM | Converts a serial number to a number representing where the week falls numerically with a year | WEEKNUM('3/9/2012', 2) | 11 |
WORKDAY | Returns the serial number of the date before or after a specified number of workdays | 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) |
FINANCIAL FORMULAS
| Formula | Description | Example | Result |
|---|---|---|---|
IRR | Returns the internal rate of return for a series of cash flows | IRR([-75000,12000,15000,18000,21000,24000], 0.075) | 0.05715142887178447 |
ACCRINT | Returns the accrued interest for a security that pays periodic interest | ACCRINT('01/01/2011', '02/01/2011', '07/01/2014', 0.1, 1000, 1, 0) | 350 |
NPER | Returns the number of periods for an investment | NPER(0.1/12, -100, -1000, 10000, 0) | 63.39385422740764 |
STATISTICAL FORMULAS
| Formula | Description | Example | Result |
|---|---|---|---|
COUNT | Counts how many values are in the list of arguments | COUNT([1,2], [3,4]) | 4 |
COUNTA | Counts how many values are in the list of arguments | COUNTA([1, null, 3, 'a', '', 'c']) | 4 |
COUNTUNIQUE | Count the amount of unique values | COUNTUNIQUE([1,1,2,2,3,3]) | 3 |
FORECAST | Returns a value along a linear trend
In Excel 2016, this function is replaced with FORECAST.LINEAR as part of the new Forecasting functions, but it's still available for compatibility with earlier versions. | FORECAST(30, [6,7,9,15,21], [20,28,31,38,40]) | 10.607253086419755 |
FREQUENCY | Returns a frequency distribution as a vertical array | FREQUENCY([79,85,78,85,50,81,95,88,97], [70,79,89]) | 1,2,4,2 |
MAX | Returns the maximum value in a list of arguments | MAX([0.1,0.2], [0.4,0.8], [true, false]) | 0.8 |
MEDIAN | Returns the median of the given numbers | MEDIAN([1,2,3], [4,5,6]) | 3.5 |
MIN | Returns the minimum value in a list of arguments | MIN([0.1,0.2], [0.4,0.8], [true, false]) | 0.1 |
ROLLUP FORMULAS
| Formula | Description | Example | Result |
|---|---|---|---|
FLATTEN | |||
MAX | Returns the largest of the given numbers. | ||
XOR | Returns true if and only if odd number of values are true. | ||
COUNT | Count only non-empty numeric values. If you want to count all records, use COUNTALL. | ||
ARRAYJOIN | Join all the values into a single comma-separated string. | ||
CONCATENATE | Joins together the text values into a single text value. | ||
OR | Returns true if any one of the values is true. | ||
MIN | Returns the smallest of the given numbers. | ||
COUNTA | Count the number of non-empty values. This function counts both numeric and text values. | Counts how many numbers are in the list of arguments | |
ARRAYUNIQUE | Return only unique items. | ||
ARRAYCOMPACT | Removes empty strings and null values from the array. Keeps "false" and strings that contain one or more blank characters. | ||
AVERAGE | Mean average of the values. | ||
COUNTALL | Count the number of linked records. Choosing any column of the linked table will produce the same result. This function counts all values including blank records. | ||
SUM | Sum together the values. | ||
AND | Returns true if all the values are true |