Writing formulas

Formula overview

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 10308 or as small as approximately -10308; 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:

HeightLength
2040
3550
6015

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

HeightLengthArea
2040800
35501750
6015900

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

OperatorOperation
+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
ExampleResult
8 + 513
8 - 53
8 * 540
8 / 51.6
8 ^ 532768
8 % 53

Comparison operators

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

OperatorOperation
=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.

ExampleResult
8 = 5false
8 > 5true
8 = 8true
8 > 8false
8 >= 8true
"abc" = "abc"true
"abc" = "def"false
"abc" = "ABC"false
true = truetrue
false = falsetrue
true = falsefalse
8 = "eight"false
8 = "8"false

Text operators

OperatorOperation
&converts two values to strings if necessary, then concatenates them
ExampleResult
"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 nameDescription
ABSReturns the absolute value of a number.
ACOSReturns the arc cosine of a number.
ANDReturns TRUE if all its inputs are true, and FALSE otherwise.
ASINReturns the arc sine of a number.
ATANReturns the arc tangent of a number.
ATAN2Returns the angle measured in radians between the positive x-axis and the point at the specified coordinate values.
AVERAGEReturns the arithmetic mean of all the numbers in a sequence.
CEILINGReturns the value of a number rounded down toward negative infinity.
CLASSIFYReturns the number of entries in a sequence that are equal to a specified value.
COEFFReturns a number formatted as a multiple of a constant.
CONTAINSReturns TRUE if a string contains a specified substring, and FALSE otherwise.
CORRELReturns the correlation between two sequences of numbers.
COSReturns the cosine of an angle that is measured in radians.
COSHReturns the hyperbolic cosine of a number.
COUNTReturns the number of entries in a sequence.
COVReturns the sample covariance of two sequences of numbers.
CURRENCYReturns a number formatted as an amount of the local currency.
DATEReturns a date/time value.
DATEISOReturns a string representing a date/time value, formatted using the international (ISO) formatting standard.
DATESTRINGReturns a string representing the date of a date/time value, formatted using the local conventions.
DATETIMESTRINGReturns a string representing a date/time value, formatted using the local conventions.
DAYReturns the day of the month of a date/time value.
DECIMALReturns a number formatted with the specified number of digits.
DEGREESReturns an angle measured in degrees, converted from radians.
EXPReturns e (the base of the natural logarithm) raised to the specified power.
FILTERReturns the specified value only if a specified condition is true.
FINDReturns the position within a string of a specified substring.
FIRSTReturns the first value in the input sequence.
FLOORReturns the value of a number rounded up toward positive infinity.
GEOMEANReturns the geometric mean of all the numbers in a sequence.
HOURReturns the hour of a date/time value.
HYPOTReturns the length of the hypotenuse of a right triangle with sides with the specified lengths.
IFReturns one of two specified values, depending on whether a specified condition is true or false.
ISODATEReturns the date/time value represented in the international (ISO) standard format.
ISVALUEReturns TRUE if its input is any non-nil value, and FALSE otherwise.
LASTReturns the last value in the input sequence.
LENGTHReturns the number of characters in a string.
LNReturns the natural logarithm of a number.
LOGReturns the logarithm of a number using a specified base.
LOG10Returns the base 10 logarithm of a number.
LOWERReturns a string, converted to lowercase.
MAXReturns the largest number in a sequence.
MEDIANReturns the median of all the numbers in a sequence.
METRICReturns a number formatted as a quantity of the specified unit using metric prefixes.
MINReturns the smallest number in a sequence.
MINUTEReturns the minute of a date/time value.
MODReturns the remainder of a division.
MODEReturns the item that appears the most times in a sequence.
MONTHReturns the month of a date/time value.
NEXTReturns a sequence, with its first element removed and all other elements moved forward.
NOTReturns FALSE if its input is true, and FALSE otherwise.
NTHReturns the value at the specified index of the input sequence.
ORReturns TRUE if any of its inputs are true, and FALSE otherwise.
PERCENTReturns a number formatted as a percentage.
POWReturns a number raised to the specified power.
PREVReturns a sequence, with its last element removed and all other elements moved backward.
PRODUCTReturns the product of all the numbers in a sequence.
RADIANSReturns an angle measured in radians, converted from degrees.
RANGEReturns a sequence containing the integers between specified minimum and maximum values.
RANKReturns a sequence, with its elements replaced by their sorting rank.
REPLACEReturns a string, with all occurrences of one substring replaced by another substring.
REVERSEReturns a sequence, with its elements put into reverse order.
ROUNDReturns the value of a number rounded to a specified precision.
SCALEReturns a number formatted as a multiple of a scale value (K, M, B, T).
SCIENTIFICReturns a number formatted in scientific notation.
SECONDReturns the second of a date/time value.
SEQUENCEReturns a sequence containing the specified values.
SINReturns the sine of an angle that is measured in radians.
SINHReturns the hyperbolic sine of a number.
SORTReturns a sequence, with its elements put into a sorted order.
SPANReturns a sequence containing a specified number of evenly-spaced numbers between specified minimum and maximum values.
SQRTReturns the square root of a number.
STDReturns the sample standard deviation of all the numbers in a sequence.
SUBReturns a substring from the specified range of a string.
SUMReturns the sum of all the numbers in a sequence.
TANReturns the tangent of an angle that is measured in radians.
TANHReturns the hyperbolic tangent of a number.
TIMEReturns a date/time value.
TIMESTRINGReturns a string representing the time of a date/time value, formatted using the local conventions.
UPPERReturns a string, converted to uppercase.
VARReturns the sample variance of a sequence of numbers.
WEEKDAYReturns the day of the week of a date/time value, using 1 for Sundays, 2 for Mondays, etc.
YEARReturns the year of a date/time value.
YEARFReturns 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.