Developed by James Rodovich
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 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 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 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.
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:
You could create a new field, named Area, and populate its values by entering the formula $"Height" * $"Length" as the field’s model.
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.
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 perform arithmetic operations on numbers and result in other numbers.
|+||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|
|8 + 5||13|
|8 - 5||3|
|8 * 5||40|
|8 / 5||1.6|
|8 ^ 5||32768|
|8 % 5||3|
Comparison operators compare two values and result in either true or false.
|=||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.
|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|
|&||converts two values to strings if necessary, then concatenates them|
|"abc" & "def"||"abcdef"|
|3 & " people"||"3 people"|
|8 & 5||"85"|
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.
The following functions can be used in formulas.
|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.