Writing formulas

Formulas perform calculations using values in a dataset. You can use a formula to provide default values in a dataset field or to provide values to be displayed in a graph, table, or chart. You can also use formulas to perform calculations in the calculator pane.

Formulas are comprised of values, operators, and functions.

Values

Values can be numbers (such as 2, -100, or 1.25), text strings (such as "Chicago"), or booleans (true or false) that you enter directly into the formula. Additionally, you can specify dataset fields that contain the values to use. If you specify dataset fields, the calculation will be updated automatically whenever the values in the dataset change.

Numbers

Numbers can be positive, negative, or zero, whole numbers or decimals. They can be as large as approximately 10³⁰⁸ or as small as approximately -10³⁰⁸; typically, numbers have about fifteen digits of precision. Positive and negative infinity can also be used.

For number values that you type into a formula, you must omit thousands separators. For example, type 12345678 instead of 12,345,678. Additionally, you must use a period as your decimal separator regardless of your Language & Text system preferences. For example, type 1.99999 instead of 1,99999.

Strings

Strings can contain zero or more of any type of printable characters, including letters, numbers, spaces, and punctuation marks. For strings that you type into a formula, you must enclose the string’s characters in quotation marks. For example, you can type "Chicago" or "875 N. Michigan Avenue".

You can include a quotation mark in a string by placing a backslash before the quotation mark: "Quoth the raven, \"Nevermore.\"". You can include a backslash in a string by typing two consecutive backslashes.

Booleans

Boolean values can be true or false.

Using values from a dataset

Formulas can operate on values that are contained in a dataset. Whenever the values in the dataset change, the formula’s calculation will be updated automatically to show the new result.

Typically, when you use values from a dataset in a formula, you will want to perform the same calculation for each entry in the dataset. Therefore, you will want to refer to an entire dataset field, rather than individual values in individual entries. To do this, you type a dollar sign ($) followed by the name of the dataset field enclosed in quotation marks. For example, if your dataset has a field named Height, you can refer to the values in this field by typing $"Height" into your formula.

When you refer to a dataset field, the values of that field for every entry are collected into a sequence, and the formula will be performed using every value in the sequence.

For example, suppose you have a dataset containing fields named Height and Length, containing three entries with the following values:

Height	Length
20	40
35	50
60	15

You could create a new field, named Area, and populate its values by entering the formula $"Height" * $"Length" as the field’s model.

Height	Length	Area
20	40	800
35	50	1750
60	15	900

You can then modify, add, or remove entries in the dataset without needing to update the formula or manually enter values in the Area field.

For more about datasets, see the “Entering data” guide.

Operators

An operator is a symbol (such as + or =) that performs an operation on a value or values. For example, the operator + adds two values, and the operator = tests whether two values are equal. In the formula 100 + 20, the two number values are added together, resulting in 120; in the formula "Chicago" = "a", the two string values are compared and found to be not equal, resulting in false.

Arithmetic operators

Arithmetic operators perform arithmetic operations on numbers and result in other numbers.

Operator	Operation
+	adds two values
-	subtracts one value from another, or negates a value
*	multiplies two values
/	divides one value by another
^	raises one value to the power of another
%	calculates the remainder of a division

Example	Result
8 + 5	13
8 - 5	3
8 * 5	40
8 / 5	1.6
8 ^ 5	32768
8 % 5	3

Comparison operators

Comparison operators compare two values and result in either true or false.

Operator	Operation
=	tests whether two values are equal
<>	tests whether two values are unequal
>	tests whether one number value is greater than another
<	tests whether one number value is less than another
>=	tests whether one number value is greater than or equal to another
<=	tests whether one number value is less than or equal to another

Note that the = and <> operators can be used on values of any type, while >, <, >=, and <= can be used only to compare numbers. Comparisons of strings are case-sensitive. If the values being compared are not of the same type, they are considered unequal.

Example	Result
8 = 5	false
8 > 5	true
8 = 8	true
8 > 8	false
8 >= 8	true
"abc" = "abc"	true
"abc" = "def"	false
"abc" = "ABC"	false
true = true	true
false = false	true
true = false	false
8 = "eight"	false
8 = "8"	false

Text operators

Operator	Operation
&	converts two values to strings if necessary, then concatenates them

Example	Result
"abc" & "def"	"abcdef"
3 & " people"	"3 people"
8 & 5	"85"

Functions

Functions are pre-named operations that can be performed on a list of values that are specified after the function name, separated by commas and enclosed in parentheses. For example, the formula IF(true, "soup", "salad") performs the IF function on the values true, "soup", and "salad".

The values on which a function operates need not be typed directly into the formula. The values can be retrieved from dataset fields that you specify, or they can be the results of other functions or operators. For example, the formula AVERAGE($"Height") performs the AVERAGE function on the values in the dataset field named Height. The formula IF($"Height" > AVERAGE($"Height"), "tall", "short") first performs the AVERAGE function on the values in the dataset field Height, then compares the values in the Height field to the result of the AVERAGE function, and finally performs the IF function on the result of the comparison and the values "tall" and "short".

When operating on the values in a dataset field or other sequence, there are two categories of functions to consider: reducing functions and mapping functions. Reducing functions produce a single result that applies to all the values in the sequence; mapping functions produce a new sequence containing a separate result for each value in the original sequence.

Examples of reducing functions include SUM, AVERAGE, and MAX, which return the sum, the arithmetic mean, and the largest numeric element of the specified sequence, respectively.

Examples of mapping functions include ROUND, IF, and FIND. For example, when ROUND is performed on a sequence — such as a field of a dataset — the result is a sequence in which each element is the rounded value of the corresponding element of the input sequence.

Function list

The following functions can be used in formulas.

Function name	Description
ABS	Returns the absolute value of a number.
ACOS	Returns the arc cosine of a number.
AND	Returns TRUE if all its inputs are true, and FALSE otherwise.
ASIN	Returns the arc sine of a number.
ATAN	Returns the arc tangent of a number.
ATAN2	Returns the angle measured in radians between the positive x-axis and the point at the specified coordinate values.
AVERAGE	Returns the arithmetic mean of numbers in a sequence.
CEILING	Returns the value of a number rounded up toward positive infinity.
CLASSIFY	Returns the number of values in a sequence that are equal to a specified value.
COEFF	Returns a number formatted as a multiple of a constant.
COMPACT	Returns a sequence, with any blanks removed.
CONTAINS	Returns TRUE if a string contains a specified substring, and FALSE otherwise.
CORREL	Returns the correlation between two sequences of numbers.
COS	Returns the cosine of an angle that is measured in radians.
COSH	Returns the hyperbolic cosine of a number.
COUNT	Returns the number of values in a sequence that are true.
COV	Returns the sample covariance of two sequences of numbers.
CURRENCY	Returns a number formatted as an amount of the local currency.
DATE	Returns a date/time value.
DATEISO	Returns a string representing a date/time value, formatted using the international (ISO) formatting standard.
DATESTRING	Returns a string representing the date of a date/time value, formatted using the local conventions.
DATETIMESTRING	Returns a string representing a date/time value, formatted using the local conventions.
DAY	Returns the day of the month of a date/time value.
DECIMAL	Returns a number formatted with the specified number of digits.
DEGREES	Returns an angle measured in degrees, converted from radians.
EXP	Returns e (the base of the natural logarithm) raised to the specified power.
FILTER	Returns the specified value only if a specified condition is true.
FIND	Returns the position within a string of a specified substring.
FIRST	Returns the first value in the input sequence.
FLOOR	Returns the value of a number rounded down toward negative infinity.
GEOMEAN	Returns the geometric mean of numbers in a sequence.
HOUR	Returns the hour of a date/time value.
HYPOT	Returns the length of the hypotenuse of a right triangle with sides with the specified lengths.
IF	Returns one of two specified values, depending on whether a specified condition is true or false.
ISODATE	Returns the date/time value represented in the international (ISO) standard format.
ISVALUE	Returns TRUE if its input is any non-nil value, and FALSE otherwise.
LAST	Returns the last value in the input sequence.
LENGTH	Returns the number of characters in a string.
LN	Returns the natural logarithm of a number.
LOG	Returns the logarithm of a number using a specified base.
LOG10	Returns the base 10 logarithm of a number.
LOWER	Returns a string, converted to lowercase.
MAX	Returns the largest number in a sequence.
MEDIAN	Returns the median of all the numbers in a sequence.
METRIC	Returns a number formatted as a quantity of the specified unit using metric prefixes.
MIN	Returns the smallest number in a sequence.
MINUTE	Returns the minute of a date/time value.
MOD	Returns the remainder of a division.
MODE	Returns the item that appears the most times in a sequence.
MONTH	Returns the month of a date/time value.
NEXT	Returns a sequence, with its first element removed and all other elements moved forward.
NOT	Returns FALSE if its input is true, and TRUE otherwise.
NTH	Returns the value at the specified index of the input sequence.
OR	Returns TRUE if any of its inputs are true, and FALSE otherwise.
PERCENT	Returns a number formatted as a percentage.
POW	Returns a number raised to the specified power.
PREV	Returns a sequence, with its last element removed and all other elements moved backward.
PRODUCT	Returns the product of numbers in a sequence.
QUANTILE	Returns a sequence, with its elements replaced by the numbers of the quantiles in which they rank.
RADIANS	Returns an angle measured in radians, converted from degrees.
RANGE	Returns a sequence containing the integers between specified minimum and maximum values.
RANK	Returns a sequence, with its elements replaced by their sorting rank.
REPLACE	Returns a string, with all occurrences of one substring replaced by another substring.
REVERSE	Returns a sequence, with its elements put into reverse order.
ROUND	Returns the value of a number rounded to a specified precision.
SCALE	Returns a number formatted as a multiple of a scale value (K, M, B, T).
SCIENTIFIC	Returns a number formatted in scientific notation.
SECOND	Returns the second of a date/time value.
SEQUENCE	Returns a sequence containing the specified values.
SIN	Returns the sine of an angle that is measured in radians.
SINH	Returns the hyperbolic sine of a number.
SORT	Returns a sequence, with its elements put into a sorted order.
SPAN	Returns a sequence containing a specified number of evenly-spaced numbers between specified minimum and maximum values.
SQRT	Returns the square root of a number.
STD	Returns the sample standard deviation of numbers in a sequence.
SUB	Returns a substring from the specified range of a string.
SUM	Returns the sum of numbers in a sequence.
TAN	Returns the tangent of an angle that is measured in radians.
TANH	Returns the hyperbolic tangent of a number.
TIME	Returns a date/time value.
TIMESTRING	Returns a string representing the time of a date/time value, formatted using the local conventions.
UNIQUE	Returns a sequence containing only the first occurrence of each unique value.
UPPER	Returns a string, converted to uppercase.
VAR	Returns the sample variance of a sequence of numbers.
WEEKDAY	Returns the day of the week of a date/time value, using 1 for Sundays, 2 for Mondays, etc.
YEAR	Returns the year of a date/time value.
YEARF	Returns the year of a date/time value, with a fractional part representing the fraction of the year that is before the date/time.

For more detailed descriptions, including descriptions of the values on which the functions operate, open Lively Logic’s formula reference by choosing Window ▸ Formula Reference.