Select Page

On2Air Formulas Database

FormulaDescriptionExampleResultTypeOn2Air Field Type
&
Use & to separate fields or when adding \n or other Markdown formatting in the On2Air Formula field
FORMATTING
*
Multiply two numeric values
{{Hours}}*{{Hourly Rate}} 
MATH
FORMULA
+
Add together two numeric values
{{Rate}} + 2
MATH
FORMULA
-
Subtract two numeric values
{{Discount}} - 3.00
MATH
FORMULA
/
Divide two numeric values
{{Discount}} / {{No. of Products}}
MATH
FORMULA
\n
Use as a line break to put field data on separate lines. Use & to add spaces
JOIN({Business Name} & "\n" & {Email})
BlueBird Marketing [email protected]
FORMATTING
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
FINANCIAL
FORMULA
AND
Returns TRUE if all of its arguments are TRUE
AND(true, false, true)
false
LOGICAL
FORMULA
AND
Returns true if all the values are true
ROLLUP
FORMULA
ARRAYCOMPACT
Removes empty strings and null values from the array. Keeps "false" and strings that contain one or more blank characters.
ROLLUP
FORMULA
ARRAYJOIN
Join all the values into a single comma-separated string.
ROLLUP
FORMULA
ARRAYUNIQUE
Return only unique items.
ROLLUP
FORMULA
AVERAGE
Mean average of the values.
ROLLUP
FORMULA
CLEAN
Removes all nonprintable characters from text
CLEAN('Monthly report')
Monthly report
TEXT
FORMULA
CODE
Returns a numeric code for the first character in a text string
CODE('A')
65
TEXT
FORMULA
CONCATENATE
Join values together
CONCATENATE({{First Name}}, ' ', {{Last Name}})
Andreas Hauser
TEXT
FORMULA
CONCATENATE
Joins together the text values into a single text value.
ROLLUP
FORMULA
COUNT
Counts how many values are in the list of arguments
COUNT([1,2], [3,4])
4
STATISTICAL
FORMULA
COUNT
Count only non-empty numeric values. If you want to count all records, use COUNTALL.
ROLLUP
FORMULA
COUNTA
Counts how many values are in the list of arguments
COUNTA([1, null, 3, 'a', '', 'c'])
4
STATISTICAL
FORMULA
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
ROLLUP
FORMULA
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.
ROLLUP
FORMULA
COUNTUNIQUE
Count the amount of unique values
COUNTUNIQUE([1,1,2,2,3,3])
3
STATISTICAL
FORMULA
DATE
Returns the serial number of a particular date
DATE(2008, 7, 8)
Tue Jul 08 2008 00:00:00 GMT-0700 (PDT)
DATE
FORMULA
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)
DATE
FORMULA
DAY
Converts a serial number to a day of the month
DAY('15-Apr-11')
15
DATE
FORMULA
DAYS
Returns the number of days between two dates
DAYS('3/15/11', '2/1/11')
42
DATE
FORMULA
DIFFERENCE
ARRAY
Enable Markdown in On2Air Formula Field
In On2Air Formula field, open Extra Options menu, click View Advanced Options. In the render as field, type markdown
FORMATTINGMARKDOWN
EXACT
Checks to see if two text values are identical
EXACT('Word', 'word')
false
TEXT
FORMULA
false
Returns the logical value FALSE
FALSE()
false
LOGICAL
FORMULA
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
TEXT
FORMULA
FIRST
ARRAY
FLATTEN
ROLLUP
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
STATISTICAL
FORMULA
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
STATISTICAL
FORMULA
HOUR
Converts a serial number to an hour
HOUR('7/18/2011 7:45:00 AM')
7
DATE
FORMULA
IF
Specifies a logical test to perform
IF(true, 'Hello!', 'Goodbye!')
Hello!
LOGICAL
FORMULA
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
LOGICAL
FORMULA
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
LOGICAL
FORMULA
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!
LOGICAL
FORMULA
INCLUDES
ARRAY
INTERSECTION
Creates an array of unique values that are included. The order and references of result values are determined by the first array
INTERSECTION([2, 1], [2, 3])
2
ARRAY
IRR
Returns the internal rate of return for a series of cash flows
IRR([-75000,12000,15000,18000,21000,24000], 0.075)
0.05715142887178447
FINANCIAL
FORMULA
ISOWEEKNUM
Returns the number of the ISO week number of the year for a given date
ISOWEEKNUM('3/9/2012')
10
DATE
FORMULA
LAST
ARRAY
LEFT
Returns the leftmost characters from a text value
LEFT('Sale Price', 4)
Sale
TEXT
FORMULA
LEN
Returns the number of characters in a text string
LEN('Phoenix, AZ')
11
TEXT
FORMULA
LOWER
Converts text to lowercase
LOWER('E. E. Cummings')
e. e. cummings
TEXT
FORMULA
MAX
Returns the maximum value in a list of arguments
MAX([0.1,0.2], [0.4,0.8], [true, false])
0.8
STATISTICAL
FORMULA
MAX
Returns the largest of the given numbers.
ROLLUP
FORMULA
MEDIAN
Returns the median of the given numbers
MEDIAN([1,2,3], [4,5,6])
3.5
STATISTICAL
FORMULA
MID
Returns a specific number of characters from a text string starting at the position you specify
MID('Fluid Flow', 7, 20)
Flow
TEXT
FORMULA
MIN
Returns the minimum value in a list of arguments
MIN([0.1,0.2], [0.4,0.8], [true, false])
0.1
STATISTICAL
FORMULA
MIN
Returns the smallest of the given numbers.
ROLLUP
FORMULA
MINUTE
Converts a serial number to a minute
MINUTE('2/1/2011 12:45:00 PM')
45
DATE
FORMULA
MONTH
Converts a serial number to a month
MONTH('15-Apr-11')
4
DATE
FORMULA
NOT
Reverses the logic of its argument
NOT(true)
false
LOGICAL
FORMULA
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)
DATE
FORMULA
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)
DATE
FORMULA
NPER
Returns the number of periods for an investment
NPER(0.1/12, -100, -1000, 10000, 0)
63.39385422740764
FINANCIAL
FORMULA
NTH
ARRAY
OR
Returns TRUE if any argument is TRUE
OR(true, false, true)
true
LOGICAL
FORMULA
OR
Returns true if any one of the values is true.
ROLLUP
FORMULA
PRODUCT
Multiply numeric values
PRODUCT(5, 15, 30)
2250
MATH
FORMULA
PROPER
Capitalizes the first letter in each word of a text value
PROPER('this is a TITLE')
This Is A Title
TEXT
FORMULA
REGEXEXTRACT
REGEXEXTRACT('Palo Alto', 'Alto')
Alto
TEXT
FORMULA
REGEXMATCH
REGEXMATCH('Palo Alto', 'Alto')
true
TEXT
FORMULA
REGEXREPLACE
REGEXREPLACE('Sutoiku', 'utoiku', 'TOIC')
STOIC
TEXT
FORMULA
REPLACE
Replaces characters within text
REPLACE('abcdefghijk', 6, 5, '*')
abcde*k
TEXT
FORMULA
REPT
Repeats text a given number of times
REPT('*-', 3)
*-*-*-
TEXT
FORMULA
REVERSE
ARRAY
RIGHT
Returns the rightmost characters from a text value
RIGHT('Sale Price', 5)
Price
TEXT
FORMULA
ROUND
Rounds a number to a specified number of digits
ROUND(626.3, -3)
1000
MATH
FORMULA
ROUNDDOWN
Rounds a number down, toward zero
ROUNDDOWN(-3.14159, 2)
-3.14
MATH
FORMULA
ROUNDUP
Rounds a number up, away from zero
ROUNDUP(-3.14159, 2)
-3.15
MATH
FORMULA
SAMPLE
Gets a random element from collection
ARRAY
SAMPLE_SIZE
Gets n random elements at unique keys from collection up to the size of collection
SAMPLE_SIZE([1, 2, 3], 2); SAMPLE_SIZE([1, 2, 3], 4)
3, 1 2, 3, 1
ARRAY
SECOND
Converts a serial number to a second
SECOND('2/1/2011 4:48:18 PM')
18
DATE
FORMULA
SECOND
Converts a serial number to a second
SECOND('2/1/2011 4:48:18 PM')
18
DATE
FORMULA
SHUFFLE
ARRAY
SORT_ASC
ARRAY
SORT_DESC
ARRAY
SPLIT
SPLIT('A,B,C', ',')
A,B,C
TEXT
FORMULA
SUBSTITUTE
Substitutes new text for old text in a text string
SUBSTITUTE('Quarter 1, 2011', '1', '2', 3)
Quarter 1, 2012
TEXT
FORMULA
SUM
Adds the cells specified by a given criteria
SUM(-5, 15, 32, 'Hello World!')
42
MATH
FORMULA
SUM
Sum together the values.
ROLLUP
FORMULA
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
MATH
FORMULA
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
LOGICAL
FORMULA
TODAY
Returns the serial number of today's date
TODAY()
Thu Feb 20 2020 23:02:55 GMT+0100 (Central European Standard Time)
DATE
FORMULA
true
Returns the logical value TRUE
TRUE()
true
LOGICAL
FORMULA
UNICODE
Returns the number (code point) that corresponds to the first character of the text
UNICODE('B')
66
TEXT
FORMULA
UPPER
Converts text to uppercase
UPPER('total')
TOTAL
TEXT
FORMULA
WEEKDAY
Converts a serial number to a day of the week
WEEKDAY('2/14/2008', 3)
3
DATE
FORMULA
WEEKNUM
Converts a serial number to a number representing where the week falls numerically with a year
WEEKNUM('3/9/2012', 2)
11
DATE
FORMULA
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)
DATE
FORMULA
WORKDAYINTL
WORKDAYINTL('1/1/2012', 30, 17)
Sun Feb 05 2012 00:00:00 GMT-0800 (PST)
DATE
FORMULA
XOR
Returns true if and only if odd number of values are true.
ROLLUP
FORMULA
YEAR
Converts a serial number to a year
YEAR('7/5/2008')
2008
DATE
FORMULA
“ “
Use quotations to put space in between field names, to use static text, or around \n
JOIN({Business Name} & " " & {Email})
BlueBird Marketing [email protected]
FORMATTING