On2Air Formulas Database

Use & to separate fields or when adding \n or other Markdown formatting in the On2Air Formula field

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}}

Use as a line break to put field data on separate lines. Use & to add spaces

JOIN({Business Name} & "\n" & {Email})

BlueBird Marketing info@bluebird.com

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

Returns TRUE if all of its arguments are TRUE

AND(true, false, true)

false

Returns true if all the values are true

Removes empty strings and null values from the array. Keeps "false" and strings that contain one or more blank characters.

Join all the values into a single comma-separated string.

Return only unique items.

Mean average of the values.

Removes all nonprintable characters from text

CLEAN('Monthly report')

Monthly report

Returns a numeric code for the first character in a text string

CODE('A')

65

Join values together

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

Andreas Hauser

Joins together the text values into a single text value.

Counts how many values are in the list of arguments

COUNT([1,2], [3,4])

4

Count only non-empty numeric values. If you want to count all records, use COUNTALL.

Counts how many values are in the list of arguments

COUNTA([1, null, 3, 'a', '', 'c'])

4

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

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.

Count the amount of unique values

COUNTUNIQUE([1,1,2,2,3,3])

3

Returns the serial number of a particular date

DATE(2008, 7, 8)

Tue Jul 08 2008 00:00:00 GMT-0700 (PDT)

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)

Converts a serial number to a day of the month

DAY('15-Apr-11')

15

Returns the number of days between two dates

DAYS('3/15/11', '2/1/11')

42

In On2Air Formula field, open Extra Options menu, click View Advanced Options. In the render as field, type markdown

Checks to see if two text values are identical

EXACT('Word', 'word')

false

Returns the logical value FALSE

FALSE()

false

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

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

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

Converts a serial number to an hour

HOUR('7/18/2011 7:45:00 AM')

7

Specifies a logical test to perform

IF(true, 'Hello!', 'Goodbye!')

Hello!

Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula

IFERROR('#DIV/0!', 'Error')

Error

Returns the value you specify if the expression resolves to #N/A, otherwise returns the result of the expression

IFNA('#N/A', 'Error')

Error

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!

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

Returns the internal rate of return for a series of cash flows

IRR([-75000,12000,15000,18000,21000,24000], 0.075)

0.05715142887178447

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

ISOWEEKNUM('3/9/2012')

10

Returns the leftmost characters from a text value

LEFT('Sale Price', 4)

Sale

Returns the number of characters in a text string

LEN('Phoenix, AZ')

11

Converts text to lowercase

LOWER('E. E. Cummings')

e. e. cummings

Returns the maximum value in a list of arguments

MAX([0.1,0.2], [0.4,0.8], [true, false])

0.8

Returns the largest of the given numbers.

Returns the median of the given numbers

MEDIAN([1,2,3], [4,5,6])

3.5

Returns a specific number of characters from a text string starting at the position you specify

MID('Fluid Flow', 7, 20)

Flow

Returns the minimum value in a list of arguments

MIN([0.1,0.2], [0.4,0.8], [true, false])

0.1

Returns the smallest of the given numbers.

Converts a serial number to a minute

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

45

Converts a serial number to a month

MONTH('15-Apr-11')

4

Reverses the logic of its argument

NOT(true)

false

Returns the serial number of the current date and time

NOW()

Thu Feb 20 2020 23:02:55 GMT+0100 (Central European Standard Time)

Returns the serial number of the current date and time

NOW()

Thu Feb 20 2020 23:02:55 GMT+0100 (Central European Standard Time)

Returns the number of periods for an investment

NPER(0.1/12, -100, -1000, 10000, 0)

63.39385422740764

Returns TRUE if any argument is TRUE

OR(true, false, true)

true

Returns true if any one of the values is true.

Multiply numeric values

PRODUCT(5, 15, 30)

2250

Capitalizes the first letter in each word of a text value

PROPER('this is a TITLE')

This Is A Title

REGEXEXTRACT('Palo Alto', 'Alto')

Alto

REGEXMATCH('Palo Alto', 'Alto')

true

REGEXREPLACE('Sutoiku', 'utoiku', 'TOIC')

STOIC

Replaces characters within text

REPLACE('abcdefghijk', 6, 5, '*')

abcde*k

Repeats text a given number of times

REPT('*-', 3)

*-*-*-

Returns the rightmost characters from a text value

RIGHT('Sale Price', 5)

Price

Rounds a number to a specified number of digits

ROUND(626.3, -3)

1000

Rounds a number down, toward zero

ROUNDDOWN(-3.14159, 2)

-3.14

Rounds a number up, away from zero

ROUNDUP(-3.14159, 2)

-3.15

Gets a random element from collection

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

Converts a serial number to a second

SECOND('2/1/2011 4:48:18 PM')

18

Converts a serial number to a second

SECOND('2/1/2011 4:48:18 PM')

18

SPLIT('A,B,C', ',')

A,B,C

Substitutes new text for old text in a text string

SUBSTITUTE('Quarter 1, 2011', '1', '2', 3)

Quarter 1, 2012

Adds the cells specified by a given criteria

SUM(-5, 15, 32, 'Hello World!')

42

Sum together the values.

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

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

Returns the serial number of today's date

TODAY()

Thu Feb 20 2020 23:02:55 GMT+0100 (Central European Standard Time)

Returns the logical value TRUE

TRUE()

true

Returns the number (code point) that corresponds to the first character of the text

UNICODE('B')

66

Converts text to uppercase

UPPER('total')

TOTAL

Converts a serial number to a day of the week

WEEKDAY('2/14/2008', 3)

3

Converts a serial number to a number representing where the week falls numerically with a year

WEEKNUM('3/9/2012', 2)

11

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('1/1/2012', 30, 17)

Sun Feb 05 2012 00:00:00 GMT-0800 (PST)

Returns true if and only if odd number of values are true.

Converts a serial number to a year

YEAR('7/5/2008')

2008

Use quotations to put space in between field names, to use static text, or around \n

JOIN({Business Name} & " " & {Email})

BlueBird Marketing info@bluebird.com