Calculations and Formulas - Other

🔴 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.

To use On2Air Formulas, find your formula and use the Airtable field variable as the value like you would in Airtable.

Copy

CONCATENATE({First Name}, ' ',{Last Name})

RESULT
Mark Smith

💡

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

Subtract two numeric values

{{Discount}} - 3.00

Multiply two numeric values

{{Hours}}*{{Hourly Rate}}

Divide two numeric values

{{Discount}} / {{No. of Products}}

Add together two numeric values

{{Rate}} + 2

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!’)

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’)

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!

Returns TRUE if any argument is TRUE

OR(true, false, 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

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

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’)

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)

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’)

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’)

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’)

DAYS

Returns the number of days between two dates

DAYS(‘3/15/11’, ‘2/1/11’)

HOUR

Converts a serial number to an hour

HOUR(‘7/18/2011 7:45:00 AM’)

MINUTE

Converts a serial number to a minute

MINUTE(‘2/1/2011 12:45:00 PM’)

ISOWEEKNUM

Returns the number of the ISO week number of the year for a given date

ISOWEEKNUM(‘3/9/2012’)

MONTH

Converts a serial number to a month

MONTH(‘15-Apr-11’)

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’)

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’)

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)

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)

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])

COUNTA

Counts how many values are in the list of arguments

COUNTA([1, null, 3, ‘a’, ”, ‘c’])

COUNTUNIQUE

Count the amount of unique values

COUNTUNIQUE([1,1,2,2,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.

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