Lively Logic

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

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.

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 down toward negative infinity.

CLASSIFY

Returns the number of entries 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 entries 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 up toward positive 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.