Formulas and Calculations

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

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]

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

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

BlueBird Marketing [email protected]

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

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

Multiply numeric values

PRODUCT(5, 15, 30)

2250

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

Adds the cells specified by a given criteria

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

42

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

Reverses the logic of its argument

NOT(true)

false

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

Returns TRUE if any argument is TRUE

OR(true, false, true)

true

Returns the logical value TRUE

TRUE()

true

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!

Returns TRUE if all of its arguments are TRUE

AND(true, false, true)

false

Returns the logical value FALSE

FALSE()

false

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

Checks to see if two text values are identical

EXACT('Word', 'word')

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 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 a specific number of characters from a text string starting at the position you specify

MID('Fluid Flow', 7, 20)

Flow

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

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

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

UNICODE('B')

66

Converts text to uppercase

UPPER('total')

TOTAL

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

Converts a serial number to an hour

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

7

Converts a serial number to a minute

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

45

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

ISOWEEKNUM('3/9/2012')

10

Converts a serial number to a month

MONTH('15-Apr-11')

4

Returns the serial number of the current date and time

NOW()

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

Converts a serial number to a second

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

18

Returns the serial number of the current date and time

NOW()

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

Converts a serial number to a second

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

18

Returns the serial number of today's date

TODAY()

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

Converts a serial number to a day of the week

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

3

Converts a serial number to a year

YEAR('7/5/2008')

2008

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 the internal rate of return for a series of cash flows

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

0.05715142887178447

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 the number of periods for an investment

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

63.39385422740764

Counts how many values are in the list of arguments

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

4

Counts how many values are in the list of arguments

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

4

Count the amount of unique values

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

3

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

Returns the maximum value in a list of arguments

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

0.8

Returns the median of the given numbers

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

3.5

Returns the minimum value in a list of arguments

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

0.1

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.

Joins together the text values into a single text value.

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

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.

Returns the largest of the given numbers.

Returns the smallest of the given numbers.

Returns true if any one of the values is true.

Sum together the values.

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