Getting Started

Lucid rounds out its shape data capabilities by offering formulas to users. Similar to the way spreadsheet tools allow users to analyze, adjust, and quantify their data, Lucid allows users to run certain formulas on their visual diagrams and associated metadata.

Metadata can be added to shapes in various ways, including linking data from spreadsheets, or adding custom data. Custom data can contain static values, or formulas. To add custom data to a shape, group, or page, simply select that shape and open the data panel. In the data panel, there is a section labeled "Custom Data." Click on the + button to add a custom data value. You can rename it, add a value to the property, an even insert that value onto the shape as text by clicking the +T button that appears when you hover over the property name or value.

Explore below to see a wide variety of formulas that are available and details for how they work.

Syntax

Types

=SINE(0.5 * PI * @Value)

Generally, there are five different types that can be used in the Lucid formula system: strings, numbers, booleans, arrays and objects. Additionally, there are built-in constants (such as PI) above.

String

Strings are text values that represent a sequence of characters. For example, "Hello, World", "Test", 'Demo123' are all examples of strings. Strings are constructed by paired single (') or double (") quote characters. A string is started with one of the quote characters and is terminated with the same quote character. If a string is uncompleted, the formula will be invalid.

Within a string, the same quote character can be included without stopping the string by doubling the character. Newlines can be added to a formula string in the editor by press shift+enter to add a line break; the line must still be terminated with a matching quote character, however.

Example Result
"Joe said, ""Hello, World""." Joe said, "Hello, World".
'It isn''t correct' It isn't correct
"It isn't correct" It isn't correct
'A
B'
A
B
Number

Numbers are numeric values that can be integers (-1, 0, 2, etc.) or decimal numbers (0.25, 1.234, etc.). Numbers should not include commas for place separators (for example, 1,000,000 is not a valid number).

Numbers can be defined using scientific notation, which defines the number as a factor and a power of 10. Scientific notation is in the form MeN, which translates into the value (M × 10N). For example, 1.23e4 translates into the value 12300.

Numbers can also be defined as percentages by adding a percent sign at the end (for example, 1.23%). The value used for calculations will be divided by 100 (for 1.23%, the value 0.0123 would be used), but the percentage will correctly display as a percentage in the data property editor.

Example Result
1234 1234
1.23e4 12300
1.23% 0.0123 (displayed as 1.23%)
Boolean

Booleans are either true or false and are used in boolean logic (for example, functions like IF, AND, OR, etc.).

Array

Arrays are an ordered collection of values indexed by contiguous integers. More details can be found here

Object

Objects are structures that contains key-value pairs. More details can be found here

Built-In Constants

In the Lucid formula system, some predefined constants can be used to simplify formula writing.

Constant Value Description
E 2.718281828459045 The mathematical constant e, which represents the base of the natural logarithm
PI 3.141592653589793 The mathematical constant pi. Useful when performing trigonometric functions, such as COS or TAN
NONE undefined Returns undefined

Operators

=SINE(0.5 * PI * @Value)

In the above formula, the "*" character represents a multiplication operator, and when used multiples the values together. In the example, the expression 0.5 * PI * @Value represents 0.5 times the constant PI times the shape data property value "Value".

Arithmetic Operators
Operator Examples Description
+ 1 + 2
@A + 2
@B + @C
Addition
- 1 - 2
@A - 2
@B - @C
-3
-@A
Subtraction or negation
* 1 * 2
@A * 2
@B * @C
Multiplication
/ 1 / 2
@A / 2
@B / @C
Division
^ 1 ^ 2
@A ^ 2
@B ^ @C
Exponentiation
Comparison Operators
Operator Examples Description
< 1 < 2
@A < 2
@B < @C
Less than
<= 1 <= 2
@A <= 2
@B <= @C
Less than or equal to
= 1 = 2
@A = 2
@B = @C
Equal to
>= 1 >= 2
@A >= 2
@B >= @C
Greater than or equal to
> 1 > 2
@A > 2
@B > @C
Greater than
<> 1 <> 2
@A <> 2
@B <> @C
Not equal
Text Concatenation Operators
Operator Examples Description
& "A" & "B"
@A & "C"
"D" & @B & @C
Text concatenation. Combines two string together into a single string, for example "Lucid" & "chart" produces the string "Lucidchart".

References

=SINE(0.5 * PI * @Value)

In the above formula, the "@Value" gets the value of the shape data property "Value". If the shape we are using has a property named "Value" which has the value 5, then the expression above is equivalent to:

=SINE(0.5 * PI * 5)

References can be used to access shape data properties from other shapes, such as the page or shape contained in the current shape's group.

Functions

=SINE(0.5 * PI * @Value)

Functions are operations that expect 0 or more parameters and produce some kind of output. In the above example, the function is named SINE. Functions are called using parenthesis and will return some value dependent on the function. In the case of SINE, the value returned is the trigonometric function sine for the specified value (in radians). If the shape data property @Value has the value 5, then the expression is equivalent to:

=SINE(0.5 * PI * 5)

The value of SINE at 2.5 pi radians is 1, so the expression returns 1.

Some functions take no parameters, and can be used by themselves (with or without parentheses). For example, the CURRENTSECOND function does not require parameters, and can be used by itself.

Advanced Syntax

Lambdas

Some functions require the user to specify an expression to evaluate for each item in an array parameter (for example, FILTER or MAP). These functions require more than just data to be provided, but instead expect another expression that is evaluated for each item in the array. For example, image a shape that has multiple child shapes that all have the "Value" shape data property, the follow expression will return an array which contains all children that have a value greater than 5:

=FILTER(this.children, child => child.Value > 5)

In this expression, the FILTER function is called on this.children (which returns all children for the current item), and for each child, will return a true/false value when the child's "Value" property is greater than 5. The FILTER function takes two parameters, the first is an array of values (in this case, this.children) and a expression to evaluate for each item (in this case, child => child.Value > 5).

The expression to evaluate in this case is called a lambda, and comes in the form of:

name => (operations using name)

name above tells the expression what to call each item in FILTER, and can be used as if it's a constant or a function. As another example, consider the following formula:

=MAP(ARRAY(1, 2, 3), x => x * x)

In this formula, the MAP function is called for each item in the array (the values 1, 2, and 3). For each item, the value is multiplied by itself, resulting in an array containing the square of each value: [1, 4, 9].

Nesting Lambdas

Lambdas can also be nested, for example if a function which expects a lambda parameter calls a function which itself expects a lambda in its lambda. When nesting functions that take lambdas, using different names for each lambda helps to ensure that each item can be referenced uniquely.

Consider the following formula:

=MAP(ARRAY(1, 2, 3), x => FILTER(@arr, y => y > x)

In this formula, the MAP function is called on an the array containing 1, 2, and 3. For each item in the array, the FILTER function is called, using the current shape's shape data property "arr", and filtering to include only items where the item is greater than the current value from the MAP function. For example, if @arr contained the array [1, 2, 3, 4], the above function would return: [[2, 3, 4], [3, 4], [4]]. If we walk through each step:

  1. MAP is called on [1, 2, 3]
  2. For each item in the array:
    • x = 1:
      • FILTER returns the array [1, 2, 3, 4] filtered to only include items above x (1), which is [2, 3, 4]
    • x = 2:
      • FILTER return the array filtered to only include items above x (2), which is [3, 4]
    • x = 3:
      • FILTER return the array filtered to only include items above x (3), which is [4]
    • No more items are in array, so the loop stops, returning each item found above as an array: [[2, 3, 4], [3, 4], [4]].

Shape References

CHILDREN

Gets info about all the child elements of the current point.

If you are in page data, then "children" is all items, including groups, shapes, lines and layers on the page.

If you are inside a group shape or on a layer, then "children" is all shapes within the group or layer.

NOTE: "children" only reflects the the immediate children of your current space. So, if a group shape is a "child" of your current space, then the shapes that are making up that group shape are not the "children" of the current space. They are "descendants", but NOT children. The group shape is the "child".

Syntax

=CHILDREN

Examples

In the following examples, the selected shape is a group with 4 shapes in it, with the following text and values:

Text Property 1 Property 2
Shape 1 1 10
Shape 2 4 18
Shape 3 2
Shape 4 6 14

=CHILDREN ["Shape 1", "Shape 2", Shape 3", "Shape 4"]

The array of all children of the current shape.

=CHILDREN."Property 1" [1, 4, 2, 6] The array of "Property 1" values of all children of the current shape.

=CHILDREN."Property 2" [10, 18, 14] The array of "Property 2" values of all children of the current shape.

NOTE: In the example above, the "Property 2" shape data does not exist for shape "Shape 3", so no data is included in the resulting array.

=CHILDREN."Property 3" #ERROR! The value of the shape data named "Property 3" on this shape. In the example above, the property does not exist, so the result is an error.

CONNECTED

Get a list of references to all shapes that are connected directly to the current shape, via a line.

Syntax

=CONNECTED

Examples

image

In the following examples, the selected shape is Shape 2

Text Property 1 Property 2
Shape 1 1 10
Shape 2 4 18
Shape 3 2 13
Shape 4 6
Shape 5 8 12

=CONNECTED [Shape 1, Shape 3, Shape 4] Get all shapes connected to the current shape

=CONNECTED."Property 1" [1, 2, 6] Get all shapes' shape data property "Property 1"

CONNECTEDDEEP

Get a list of references to all shapes that are connected to the current shape, via a line, as well as all shapes connected to the connected shapes, etc.

Syntax

=CONNECTEDDEEP

Examples

image

In the following examples, the selected shape is Shape 2

Text Property 1 Property 2
Shape 1 1 10
Shape 2 4 18
Shape 3 2 13
Shape 4 6
Shape 5 8 12

=CONNECTEDDEEP [Shape 1, Shape 3, Shape 4, Shape 5] Get all shapes connected to the current shape.

=CONNECTEDDEEP."Property 1" [1, 2, 6, 8] Get all shapes' shape data property "Property 1"

DESCENDANTS

Get info about all descendants of current object: children, and children of children, etc.

Syntax

=DESCENDANTS

Examples

In the following examples, the selected shape is a group with 6 shapes in it, with the following text and values

Text Property 1 Property 2
Shape 1 1 10
Shape 2 4 18
Shape 3 2 13
Shape 4 6
Shape 5 7 14
Group 6 8

"Group 6" is a group with 2 shapes in it, with the following text and values

Text Property 1 Property 2
Shape 7 5 12
Shape 8 10 15

=DESCENDANTS [Shape 1, Shape 2, Shape 3, Shape 4, Shape 5, Group 6, Shape 7, Shape 8] Get all descendants of the current object

=DESCENDANTS."Property 1" [1,4,2,6,7,8,5,10] Gets all descendants' shape data property "Property 1"

DOWNSTREAM

Gets a list of references to all shapes TO which a line has been drawn FROM the current shape

For example, for two shapes A and B, if a line is drawn from A to B, then B is upstream of A. If another shape C is added, and a line is drawn from B to C, then C is upstream of B. If another shape D is added, and a line is drawn from D to B, then B is upstream of D.

TRICKY: The directions of the arrows have no bearing on this.

Syntax

=DOWNSTREAM

Examples

image

In the following examples, the selected shape is Shape 2

Text Property 1 Property 2
Shape 1 1 10
Shape 2 4 18
Shape 3 2 13
Shape 4 6
Shape 5 8 12

=DOWNSTREAM [Shape 3, Shape 4] Get all downstream shapes from the current shape

=DOWNSTREAM."Property 1" [2, 6] Get all downstream shapes' shape data property "Property 1"

DOWNSTREAMDEEP

Gets a list of references to all shapes TO which a line has been drawn FROM the current shape, as well as all shape from that are upstream from those shapes, etc.

TRICKY: The directions of the arrows have no bearing on this.

Syntax

=DOWNSTREAMDEEP

Examples

image

In the following examples, the selected shape is Shape 2

Text Property 1 Property 2
Shape 1 1 10
Shape 2 4 18
Shape 3 2 13
Shape 4 6
Shape 5 8 12

=DOWNSTREAMDEEP [Shape 3, Shape 4, Shape 5] Get all downstream shapes from the current shape

=DOWNSTREAMDEEP."Property 1" [2, 6, 8] Get all downstream shapes' shape data property "Property 1"

PAGE

A reference to the page. Allows fast access to the page data values.

Syntax

=PAGE

Examples

=PAGE 2 The name of the page

=PAGE."Property 1" 123 The value of the shape data named "Property 1" on this page

=PAGE."Property 3" #ERROR! The value of the shape data named "Property 3" on this page. In the example above, the property does not exist on the page, so the result is an error

PARENT

Gets info about the parent element of the current item.

NOTE
If you are in page data, then there is no parent.
If you are in a shape or group shape that is on the page, then the page is the parent.
If you are in a shape or group shape that is on a layer, then the layer is the parent.
If you are in a shape or group shape that is inside a group shape, then the group shape is the parent..

Syntax

=PARENT

Examples

=PARENT Shape Get the parent shape

=PARENT."Property 1" 10 Gets the parent's shape data property "Property 1" (10)

THIS

A reference to a shape itself. It is primarily used to get things on the shape or to reference it. When it is used without referencing items on the shape, it will return the text of the shape.

Syntax

=THIS

Examples

Given that a shape has the following data:

Label Value
Property 1 Process

=THIS."Property 1 Process

PRO TIP: The alias @ can be used in place of THIS

UPSTREAM

Gets a list of references to all shapes FROM which a line has been drawn TO the current shape

For example, for two shapes A and B, if a line is drawn from A to B, then A is upstream of B. If another shape C is added, and a line is drawn from B to C, then B is upstream of C. If another shape D is added, and a line is drawn from D to B, then D is upstream of B.

TRICKY: The directions of the arrows have no bearing on this.

Syntax

=UPSTREAM

Examples

image

In the following examples, the selected shape is Shape 4

Text Property 1 Property 2
Shape 1 1 10
Shape 2 4 18
Shape 3 2 13
Shape 4 6
Shape 5 8 12

=UPSTREAM [Shape 2, Shape 5] Get all upstream shapes from the current shape

=UPSTREAM."Property 1" [4, 8] Get all upstream shapes' shape data property "Property 1"

UPSTREAMDEEP

Gets a list of references to all shapes FROM which a line has been drawn TO the current shape, as well as all shape from that are upstream from those shapes, etc.

TRICKY: The directions of the arrows have no bearing on this.

Syntax

=UPSTREAMDEEP

Examples

image

In the following examples, the selected shape is Shape 4

Text Property 1 Property 2
Shape 1 1 10
Shape 2 4 18
Shape 3 2 13
Shape 4 6
Shape 5 8 12

=UPSTREAMDEEP [Shape 2, Shape 1, Shape 5, Shape 3] Get all upstream shapes from the current shape

=UPSTREAMDEEP."Property 1" [4, 1, 8, 2] Get all upstream shapes' shape data property "Property 1"

Data References

@

A reference to a shape itself. It is primarily used to get things on the shape or to reference it. When it is used without referencing items on the shape, it will return the text of the shape.

Syntax

=@

Examples

Given that a shape has the following data:

Label Value
Property 1 Process

=@"Property 1" Process

PRO TIP: The alias @ can be used in place of THIS when accessing properties.

FIELDLOOKUP

Uses a reference key and the collection name to find a data property.

Syntax

=FIELDLOOKUP(collection, data, key)

Arguments
Argument Type Description
collection String The name of the collection to reference
data String The shape data property to reference
key String The key value to find within the data
Examples

=FIELDLOOKUP("Sheet1", "Employee Name", "123") "John Doe" Gets the employee name data property for the "Sheet1" collection, where the reference key is "123"

INDEX

Returns the value at a given position from an array, starting from 1.

Syntax

=INDEX(array, index)

=array[index]

Arguments
Argument Type Description
array Array The array of values from which to retrieve a value
index Number The position within the array to get the value

NOTE: This formula also works with deeply nested arrays.

Examples

In the following examples, the selected shape is a group with 4 shapes in it, with the following text and values:

Text Property 1 Property 2
Shape 1 1 10
Shape 2 4 18
Shape 3 2
Shape 4 6 14

=CHILDREN."Property 1"[3] 2 Returns the value in the third position of the "Property 1" in the current shape's children

=INDEX(CHILDREN."Property 2", 3) 14 Returns the third value from the list of the current shape's children's "Property 2" data value

=CHILDREN."Property 3"[0] #ERROR! Returns an error as accessing array values is 1-based (starting from 1), not 0-based

LABEL

Gets the unique shape identifier for the current shape.

Syntax

=LABEL(shape)

Arguments
Argument Type Description
shape Reference The item reference

PRO TIP: This can be used to reference specific shapes in a document.

Examples

=LABEL(THIS) "ab3756dhty!975" Gets the shape identifier for the current shape

LOOKUP

Finds data from a collection using a reference key and returns an array of all the data associated with that reference key.

Syntax

=LOOKUP(collection, key)

Arguments
Argument Type Description
collection String The name of the collection to reference
key String (Optional) The reference key for the collection
Examples

=LOOKUP("Sheet1", "123") ["123", "Steve Rogers", "Captain America"] Gets all values for a row in the "Sheet1" collection.

=LOOKUP("Sheet1", "123")."Name" "Steve Rogers" Gets the Name field for the row with the key "123" in the "Sheet1" collection.

=LOOKUP("Sheet1")."Name" ["Steve Rogers", "Tony Stark", "Bruce Banner"] Gets the Name field for all rows in the "Sheet1" collection.

Logical Statements

AND

Returns true if all of the given expressions/values are true, and false otherwise

Syntax

=AND(expr)

=AND(expr1, expr2, ..., exprN)

Arguments
Argument Type Description
exprN Boolean The value to use
Examples

=AND(false, true) false

=AND(true, true) true

=AND(1 = 1, 2 = 2) true

=AND(@"Property 1" = 1, @"Property 2" >= 4) true Checks if the shape data property "Property 1" is equal to 1 and "Property 2" is greater than or equal to 4

IF

Checks the value of an expression and returns a specified value if the expression is true, and returns a different value if not.

Syntax

=IF(expr, resultTrue, resultFalse)

Arguments
Argument Type Description
expr Boolean The expression to evaluate when determining which value to return
resultTrue Any The value to return if the expression evaluates to true
resultFalse Any The value to return if the expression evaluates to false
Examples

=IF(10 < 20, "Yes", "No") "Yes" Checks if 10 is less than 20 and returns "Yes" if so, and "No" otherwise. Because 10 is less than 20, "Yes" is returned

=IF(10 > 20, "Yes", "No") "No" Checks if 10 is greater than 20 and returns "Yes" if so, and "No" otherwise. Because 10 is not greater than 20, "No" is returned

=IF(@"Property 1" = 1, "A", "B") "A" Checks if the shape data property "Property 1" is equal to 1 and returns "A" if so, and "B" otherwise. Because "Property 1" is equal to 1, the function returns "A"

IFERROR

Checks if the expression has an error and, returns a replacement value, if so, or the original value otherwise.

Syntax

=IFERROR(expression, replacement)

Arguments
Argument Type Description
expression Any The expression to evaluate for errors
replacement Any The value to return if the expression evaluation resulted in an error
Examples

=IFERROR(1 + [1, 2, 3], 0) 0 Because an error occured during evaluation the value 0 was returned

=IFERROR(1 + 2, 0) 3 Because no error occured during evaluation the resulting value was returned

IFS

Returns the first matching condition from a list of conditions and corresponding results.

Syntax

=IFS(condition1, result1)

=IFS(condition1, result1, condition2, result2, ..., conditionN, resultN)

Arguments
Argument Type Description
condition Boolean The conditions to check
result Any The result for the corresponding condition
Examples

=IFS(0 = 1, "A", 0 = 2, "B", true, "C") "C" Check if 0 is equal to 1 (and returns "A" if so), then if 0 is equal to 2 (and returns "B" if so), then if true is true (and returns "C" if so). Because true is true by definition, the function returns "C"

=IFS(@"Property 6" > 10, "10+", @"Property 6" > 0, "0+") "0+" Checks if the shape data property "Property 6" is greater than 10 (and returns "10+" if so), then if "Property 6" is greater than 0 (and returns "0+" if so). Because "Property 6" (8) is not greater than 10, it does not return "10+"; however, it is greater than 0, so the function returns "0+"

ISEMPTY

Returns true if the expression is empty.

  • Strings passed into the function are checked if they are an empty string.
  • If an array is passed into the function, the function will return true only if every item in the flattened array is equal to the empty string.
  • If a value that is not a string or array is passed into the function, the value will be converted to a string before checking if the value is an empty string.
  • If an object is passed into the function, the function will return true only if the object has no keys.
Syntax

=ISEMPTY(expr)

Arguments
Argument Type Description
expr String, Array or Object The value to check for emptiness
Examples

=ISEMPTY("") true Checks if the string "" is empty

=ISEMPTY(123) false Checks if the number 123 is empty. 123 is converted to the string "123", which is not empty and the function returns false

=ISEMPTY(@"Property 1") false Checks if the shape data property "Property 1" is empty. Because "Property 1" is not empty (it has the value 1), the function returns false.

=ISEMPTY(@"Property 4") true Checks if the shape data property "Property 4" is empty. Because "Property 4" is empty, the function returns false.

=ISEMPTY(OBJECT("A", 2)) false Checks if the object is empty, and because the object has a key-value pair the function returns false.

ISNOTEMPTY

Returns true if the expression is not empty.

  • Strings passed into the function are checked if they are not an empty string.
  • If an array is passed into the function, the function will return true only if every item in the flattened array is not equal to the empty string.
  • If a value that is not a string or array is passed into the function, the value will be converted to a string before checking if the value is not an empty string.
  • If an object is passed into the function, the function will return true only if the object has keys.
Syntax

=ISNOTEMPTY(expr)

Arguments
Argument Type Description
expr String, Array or Object The value to check for emptiness
Examples

=ISNOTEMPTY("") false Checks if the string "" is empty

=ISNOTEMPTY(123) true Checks if the number 123 is empty. 123 is converted to the string "123", which is not empty and the function returns true.

=ISNOTEMPTY(@"Property 1") true Checks if the shape data property "Property 1" is empty. Because "Property 1" is not empty (it has the value 1), the function returns true.

=ISNOTEMPTY(@"Property 4") false Checks if the shape data property "Property 4" is empty. Because "Property 4" is empty, the function returns false.

=ISNOTEMPTY(OBJECT("A", 2)) true Checks if the object is not empty, and because the object has a key-value pair the function returns true.

NOT

Returns true if the given expression/values is false, and false if the given expression is true

Syntax

=NOT(expr)

Arguments
Argument Type Description
exprN Boolean The value to use
Examples

=NOT(false) false

=NOT(true) true

=NOT(OR(@"Property 1" = 1, @"Property 2" < 4)) false Checks if the shape data property "Property 1" is equal to 1 and "Property 2" is less than 4, and then returns false if so, true otherwise. Because "Property 1" is equal to 1, the function returns false

OR

Returns true if any of the given expressions/values are true, and false otherwise

Syntax

=OR(expr)

=OR(expr1, expr2, ..., exprN)

Arguments
Argument Type Description
exprN Boolean The value to use
Examples

=OR(false, false) false

=OR(false, true) false

=OR(true, true) true

=OR(1 = 1, 2 = 2) true

=OR(@"Property 1" = 1, @"Property 2" >= 4) true Checks if the shape data property "Property 1" is equal to 1 and "Property 2" is less than 4. Because "Property 1" is equal to 1, the function returns true (even though "Property 2" is not less than 4)

RAND

Returns a random value between 0 and 1 (inclusive of 0, exclusive of 1).

Syntax

=RAND

Example

=RAND 0.0336919

RANDBETWEEN

Returns a random integer between min and max (inclusive).

Syntax

=RANDBETWEEN(min, max)

Arguments
Argument Type Description
min Number The minimum value that the random integer can be
max Number The maximum value that the random integer can be
Example

=RANDBETWEEN(1, 5) 3

SWITCH

Returns the first matching value from a list of values and corresponding results with a specified value.

The SWITCH function expects the first argument to be the value to test, then pairs of values and results. The first value that matches the test value causes the function to return the corresponding result. If no results are found and a default value is provided, the function returns the default value; if no value is found and no default is provided, the function returns #ERROR!.

Syntax

=SWITCH(test, value1, result1)

=SWITCH(test, value1, result1, default)

=SWITCH(test, value1, result1, ..., valueN, resultN, default)

Arguments
Argument Type Description
test Any The test value to check against each value
value Any The value to compare against the test value.
result Any The result for the corresponding value.
default Any (Optional) The default value to return if no values match the test value.
Examples

=SWITCH(@"Property 1", 0, "A", 1, "B", "C") "B" Compares the shape data property value "Property 1" (1) against 0 (and returns "A" if it matches), then against 1 (and returns "B" if it matches), and then, if nothing matches, returns "C". Because "Property 1" is equal to 1, the function returns "B".

=SWITCH(@"Property 2", 0, "A", 1, "B", "C") "C" Compares the shape data property value "Property 2" (2) against 0 (and returns "A" if it matches), then against 1 (and returns "B" if it matches), and then, if nothing matches, returns "C". Because "Property 1" is equal to 2 and doesn't match any of the values, the function returns "C".

Analytical

COUNT

Counts the number of elements in the specified array.

Syntax

=COUNT(value)

Arguments
Argument Type Description
value Array An array of values to count
Examples

=COUNT([1,2,3]) 3 Counts the number of arguments (1, 2, and 3), which is equal to 3

=COUNT(children) 6 Counts all of the children on the current shape, in this case the selected shape is a group with 6 shapes in it.

=COUNT(children."Property 2") 4 Counts all children's data property values that have a value for "Property 2" (10, 18, 13, 14).

COUNTDEEP

Recursively counts the numbers of elements in the specified array.

Syntax

=COUNTDEEP(value1, value2, ..., valueN)

Arguments
Argument Type Description
value Array or Any Any value to count, which can be an array, reference or a single value.
Examples

=COUNTDEEP(1, 2, 3) 3 Counts the number of arguments (1, 2, and 3), which is equal to 3.

=COUNTDEEP(ARRAY(4, 5), 6) 3 Counts the values in the array [4, 5] and 6. Because COUNTDEEP flattens arrays, this is equivalent to counting the number of elements: 3.

=COUNTDEEP(children) 6 Counts all children on the current shape.

=COUNTDEEP(children."Property 2") 4 Counts all children's data property values that have a value for "Property 2" (10, 18, 13, 14).

COUNTIF

Counts the number of elements in an array that meet the specified condition

Syntax

=COUNTIF(array, expression)

Arguments
Argument Type Description
array Array Any value to count, which can be an array, reference or a single value
expression Boolean The expression to evaluate for each item in the flattened array, to determine whether it should be counted or not
Examples

=COUNTIF(ARRAY(1,2,3), this > 2) 1 Counts the number of arguments in the array (1, 2, and 3) which are greater than 2.

=COUNT(children, this."Property 1" > 2) 4 Counts the number of children on the current shape whose shape data property "Property 1" is greater than 2.

COUNTIFDEEP

Recursively counts the number of elements in the specified array which satisfy the given condition.

Syntax

=COUNTIFDEEP(array, expression)

Arguments
Argument Type Description
array Array Any value to count, which can be an array, reference or a single value
expression Boolean The expression to evaluate for each item in the flattened array, to determine whether it should be counted or not
Examples

=COUNTIFDEEP(ARRAY(1, 2, 3), this > 2) 1 Counts the number of arguments in the array (1, 2, and 3) which is greater than 2

=COUNTIFDEEP(ARRAY(ARRAY(4, 5), 6), this > 4) 2 Counts the number of arguments in the flattened array (4, 5, and 6) which is greater than 4

FILTER

Returns a filtered list of items from a specified array that match a specified condition.

Syntax

=FILTER(array, expression)

Arguments
Argument Type Description
array Array The array to filter
expression Boolean The expression to evaluate for each item in the flattened array, to determine whether it should be counted or not
Examples

=FILTER(ARRAY(1, 2, 3), this > 1) [2, 3] Filters the array to include numbers which are greater than 1

=FILTER(children, this."Property 1" > 2) [4, 6, 7, 8] Returns a list of children of the current shapes whose shape data property "Property 1" is greater than 2

=FILTER(ARRAY(ARRAY(1,2), ARRAY(3,4,5), ARRAY(6,7)), COUNT(this) > 2) [[3, 4, 5], [6, 7]] Returns a list of arrays which contain more than two elements

FILTERDEEP

Returns a recursively filtered list of items from a specified array that match a specified condition.

Syntax

=FILTERDEEP(array, expression)

Arguments
Argument Type Description
array Array The array to filter
expression Boolean The expression to evaluate for each item in the flattened array, to determine whether it should be counted or not
Examples

=FILTERDEEP(ARRAY(1, 2, 3), this > 1) [2, 3] Filters the array to include numbers which are greater than 1

=FILTERDEEP(children, this."Property 1" > 2) [4, 6, 7, 8] Returns a list of children of the current shapes whose shape data property "Property 1" is greater than 2

MAX

Calculates the maximum value of specified numbers.

Syntax

=MAX(number1, number2, ..., numberN)

=MAX(numberArray)

Arguments
Argument Type Description
number Number or Array A number or array of numbers to find the maximum of

If an array of values is supplied, the array will be flattened when averaging. For example, =MAX(ARRAY(1,ARRAY(2,3)),ARRAY(4,5),6) returns 6.

Examples

=MAX(1,2,3) 3 Calculates the maximum of 1, 2, and 3

=MAX(ARRAY(4,5),6) 6 Calculates the maximum of any array containing 4, 5 and the number 6. Because the MAX function flattens arrays, this is the same as the maximum of 4, 5, and 6.

=MAX(children."Property 1") 8 Calculates the maximum of all children's data property values for "Property 1" (1, 4, 2, 6, 7, 8).

MEAN

Calculates the mean (average) of specified numbers.

Syntax

=MEAN(number1, number2, ..., numberN)

=MEAN(numberArray)

Arguments
Argument Type Description
number Number or Array A number or array of numbers to average

If an array of values is supplied, the array will be flattened when averaging. For example, =MEAN(ARRAY(1,ARRAY(2,3)),ARRAY(4,5),6) returns 3.5 ((1 + 2 + 3 + 4 + 5 + 6) / 6).

Examples

=MEAN(1,2,3) 2 Calculates the mean of 1, 2, and 3

=MEAN(ARRAY(4,5),6) 5 Calculates the mean of any array containing 4, 5 and the number 6. Because the MEAN function flattens arrays, this is the same as the mean of 4, 5, and 6.

=MEAN(children."Property 1") 4.666 Calculates the mean of all children's data property values for "Property 1" (1, 4, 2, 6, 7, 8).

MIN

Calculates the minimum value of specified numbers.

Syntax

=MIN(number1, number2, ..., numberN)

=MIN(numberArray)

Arguments
Argument Type Description
number Number or Array A number or array of numbers to find the minimum of

If an array of values is supplied, the array will be flattened when averaging. For example, =MIN(ARRAY(1,ARRAY(2,3)),ARRAY(4,5),6) returns 1.

Examples

=MIN(1,2,3) 1 Calculates the minimum of 1, 2, and 3

=MIN(ARRAY(4,5),6) 4 Calculates the minimum of any array containing 4, 5 and the number 6. Because the MIN function flattens arrays, this is the same as the minimum of 4, 5, and 6.

=MIN(children."Property 1") 1 Calculates the minimum of all children's data property values for "Property 1" (1, 4, 2, 6, 7, 8).

SORT

Sorts the specified list using an optional sort order and expression to control the ordering.

Syntax

=SORT(array)

=SORT(array, order)

=SORT(array, order, expression)

Arguments
Argument Type Description
array Array The array to sort
order String (Optional) Ascending or descending order. Should be one of "ASC" or "DESC"
expression Any (Optional) An expression that, when evaluated, is used to sort the array
Examples

=SORT(ARRAY(3, 1, 2, 4, 0)) [0, 1, 2, 3, 4] Sorts the array in ascending order based on value

=SORT(children, "ASC") [Shape 1, Shape 2, Shape 3, Shape 4, Shape 5] Sorts the list of children of the current shapes in ascending order, sorted by each child's text

=SORT(children, "DESC", this."Property 1") [Shape 5, Shape 4, Shape 2, Shape 3, Shape 1] Sorts the list of children of the current shapes in descending order, sorted by each child's shape data property "Property 1"

SUM

Calculates the arithmetic sum (total) of all specified numbers.

Syntax

=SUM(number1, number2, ..., numberN)

=SUM(numberArray)

Arguments
Argument Type Description
number Number or Array A number or array of numbers to sum

If an array of values is supplied, the array will be flattened when summing. For example, =SUM(ARRAY(1,ARRAY(2,3)),ARRAY(4,5),6) returns 21 (1 + 2 + 3 + 4 + 5 + 6).

Examples

=SUM(1,2,3) 6 Calculates the sum of 1, 2, and 3

=SUM(ARRAY(4,5),6) 15 Calculates the sum of an array containing 4, 5 and the number 6

=SUM(children."Property 1") 28 Calculates the sum of all children's data property values for "Property 1" (1, 4, 2, 6, 7, 8)

Type Checks

ISARRAY

Returns true if the specified value is an array.

Syntax

=ISARRAY(value)

Arguments
Argument Type Description
value Any The value to test
Examples

=ISARRAY(children(this)) true Tests whether the result of getting children for this shape is an array

=ISARRAY("Name") false Tests whether the string "Name" is an array

=ISARRAY(ARRAY(1, 2)) true Tests whether the string [1, 2] is an array

=ISARRAY(@"Property 1") false Tests whether the shape data property @"Property 1" (123) is an array

ISBOOLEAN

Returns true if the value is a boolean value (true or false).

Syntax

=ISBOOLEAN(value, fuzzy)

Arguments
Argument Type Description
value Any The value to test
fuzzy Boolean Specify whether the test should allow conversion
Examples

=ISBOOLEAN(false) true Tests whether the value false is a boolean

=ISBOOLEAN(1, true) true Tests whether the number 1 is a boolean with fuzzy matching

=ISBOOLEAN(123) false Tests whether the number 123 is a boolean

ISCOLOR

Returns true if the specified value is a color value.

Syntax

=ISCOLOR(value, fuzzy)

Arguments
Argument Type Description
value Any The value to test
fuzzy Boolean Specify whether the test should allow conversion
Examples

=ISCOLOR(RGB(255, 0, 0)) true Tests whether the RGB color (255, 0, 0) is a color

=ISCOLOR("#112233", false) false Tests whether the hex color string "#112233" is a color value without fuzzy matching

=ISCOLOR("#112233", true) true Tests whether the hex color string "#112233" is a color value with fuzzy matching

=ISCOLOR(@"Property 1") false Tests whether the shape data property @"Property 1" (123) is a color value

ISDATE

Returns true if the specified value is a date value.

Syntax

=ISDATE(value, fuzzy)

Arguments
Argument Type Description
value Any The value to test
fuzzy Boolean Specify whether the test should allow conversion
Examples

=ISDATE(NOW) true Tests whether the current date is a date value

=ISDATE(DATE(2020, 1, 1)) true Tests whether the date January 1, 2020 is a date value

=ISDATE("2020-01-01T00:00:00", false) false Tests whether the date string "2020-01-01T00:00:00" is a date value without fuzzy matching

=ISDATE("2020-01-01T00:00:00", true) true Tests whether the date string "2020-01-01T00:00:00" is a date value with fuzzy matching

=ISDATE(@"Property 1") false Tests whether the shape data property @"Property 1" (123) is a date value

ISERROR

Returns true if the provided expression results in an error.

Syntax

=ISERROR(expression)

Arguments
Argument Type Description
expression Any The expression to test
Examples

=ISERROR(1 + [1, 2, 3]) true

=ISERROR(1 + 2) false

ISNOTERROR

Returns true if the provided expression does not result in an error.

Syntax

=ISNOTERROR(expression)

Arguments
Argument Type Description
expression Any The expression to test
Examples

=ISNOTERROR(1 + [1, 2, 3]) false

=ISNOTERROR(1 + 2) true

ISNUMBER

Returns true if the specified value is a number value

Syntax

=ISNUMBER(value, fuzzy)

Arguments
Argument Type Description
value Any The value to test
fuzzy Boolean Specify whether the test should allow conversion
Examples

=ISNUMBER(1) true Tests whether the number 1 is a number value

=ISNUMBER("Name") false Tests whether the string "Name" is a number value

=ISNUMBER("1.23", false) false Tests whether the string "1.23" is a number value without fuzzy matching

=ISNUMBER("1.23", true) true Tests whether the string "1.23" is a number value with fuzzy matching

=ISNUMBER(@"Property 1") true Tests whether the shape data property @"Property 1" (123) is a number value

ISOBJECT

Returns true if the specified value is an object value.

Syntax

=ISOBJECT(value)

Arguments
Argument Type Description
value Any The value to test
Examples

=ISOBJECT(1) true Tests whether the number 1 is an object value

=ISOBJECT("Name") false Tests whether the string "Name" is an object value

=ISOBJECT(OBJECT("A", 1, "B", 2)) true Tests whether the object with key-value pairs "A" => 1, "B" => 2 is an object value

=ISOBJECT(@"Property 1") false Tests whether the shape data property @"Property 1" (123) is an object value

ISTEXT / ISSTRING

Returns true if the specified value is a text value.

Syntax

=ISTEXT(value)

=ISSTRING(value)

Arguments
Argument Type Description
value Any The value to test
Examples

=ISTEXT(1) false Tests whether the number 1 is a text value

=ISSTRING("Name") true Tests whether the string "Name" is a text value

=ISTEXT(@"Property 1") true Tests whether the shape data property @"Property 1" ("Hello") is a text value

String/Number Checks

BETWEEN

Checks if a number is between two other numbers, inclusive. Returns true if the number is between the specified numbers, and false if it is not between those numbers.

Syntax

=BETWEEN(value, low, high)

Arguments
Argument Type Description
value Number The value to test
low Number The value to use for the low side of the inclusive range
high Number The value to use for the high side of the inclusive range
Examples

=BETWEEN(200, 0, 100) false Tests whether the value 200 is between 0 and 100, inclusive

=BETWEEN(10, 5, 15) true Tests whether the value 10 is between 5 and 15, inclusive

=BETWEEN(5, 5, 15) true Tests whether the value 5 is between 5 and 15, inclusive

=BETWEEN(20, 5, 15) false Tests whether the value 20 is between 5 and 15, inclusive

=BETWEEN(@"Property 1", 5, 15) true Tests whether the shape data property @"Property 1" (10, in this example) is between 5 and 15, inclusive

CONTAINS

Returns true if a property or string contains a value, and returns false otherwise.

Syntax

=CONTAINS(haystack, needle)

Arguments
Argument Type Description
haystack String or Array The value to look for the needle in
needle Any The value to search for in the haystack
Examples

=CONTAINS("xyz", "x") true Tests whether the string "x" is contained in the string "xyz"

=CONTAINS("xyz", "a") false Tests whether the string "a" is contained in the string "xyz"

=CONTAINS("xyz", "vxyz") false Tests whether the string "vxyz" is contained in the string "xyz"

=CONTAINS(@"Property 1", "abc") true Tests whether the string "abc" is contained in the shape data property @"Property 1" ("abcdef")

DOESNOTCONTAIN

Returns true if a property or string does not contain a value, and returns false otherwise.

Syntax

=DOESNOTCONTAIN(haystack, needle)

Arguments
Argument Type Description
haystack String or Array The value to look for the needle in
needle Any The value to search for in the haystack
Examples

=DOESNOTCONTAIN("xyz", "x") false Tests whether the string "x" is contained in the string "xyz"

=DOESNOTCONTAIN("xyz", "a") true Tests whether the string "a" is contained in the string "xyz"

=DOESNOTCONTAIN("xyz", "vxyz") true Tests whether the string "vxyz" is contained in the string "xyz"

=DOESNOTCONTAIN(@"Property 1", "abc") false Tests whether the string "abc" is contained in the shape data property @"Property 1" ("abcdef")

ENDSWITH

Returns true if a property or string ends with a value, and returns false otherwise.

Syntax

=ENDSWITH(value, test)

Arguments
Argument Type Description
value String The value to check for the ending string
test String The value to look for
Examples

=ENDSWITH("xyz", "x") false Tests whether the string "xyz" ends with "x"

=ENDSWITH("xyz", "a") false Tests whether the string "xyz" ends with "a"

=ENDSWITH("xyz", "z") true Tests whether the string "xyz" ends with "z"

=ENDSWITH(@"Property 1", "abc") false Tests whether the shape data property @"Property 1" ("abcdef") ends with the string "abc"

=ENDSWITH(@"Property 1", "def") true Tests whether the shape data property @"Property 1" ("abcdef") ends with the string "abc"

ISEVEN

Returns true if the specified number is even and false if the number is odd.

Syntax

=ISEVEN(number)

Arguments
Argument Type Description
number Number The number to test
Examples

=ISEVEN(1) false Tests whether the number 1 is even

=ISEVEN(2) true Tests whether the number 2 is even

=ISEVEN(@"Property 1") false Tests whether the shape data property @"Property 1" (123) is even.

ISODD

Returns true if the specified number is odd and false if the number is even.

Syntax

=ISODD(number)

Arguments
Argument Type Description
number Number The number to test
Examples

=ISODD(1) true Tests whether the number 1 is odd

=ISODD(2) false Tests whether the number 2 is odd

=ISODD(@"Property 1") true Tests whether the shape data property @"Property 1" (123) is odd.

NOTBETWEEN

Checks if a number is not between two other numbers, inclusive. Returns true if the number is not between the specified numbers, and false if it is between those numbers.

Syntax

=NOTBETWEEN(value, low, high)

Arguments
Argument Type Description
value Number The value to test
low Number The value to use for the low side of the inclusive range
high Number The value to use for the high side of the inclusive range
Examples

=NOTBETWEEN(200, 0, 100) true Tests whether the value 200 is not between 0 and 100, inclusive

=NOTBETWEEN(10, 5, 15) false Tests whether the value 10 is not between 5 and 15, inclusive

=NOTBETWEEN(5, 5, 15) false Tests whether the value 5 is not between 5 and 15, inclusive

=NOTBETWEEN(20, 5, 15) true Tests whether the value 20 is not between 5 and 15, inclusive

=NOTBETWEEN(@"Property 1", 5, 15) false Tests whether the shape data property @"Property 1" (10, in this example) is not between 5 and 15, inclusive

STARTSWITH

Returns true if a property or string starts with a value, and returns false otherwise.

Syntax

=STARTSWITH(value, test)

Arguments
Argument Type Description
value String The value to check for the starting string
test String The value to look for
Examples

=STARTSWITH("xyz", "x") true Tests whether the string "xyz" starts with "x"

=STARTSWITH("xyz", "a") false Tests whether the string "xyz" starts with "a"

=STARTSWITH("xyz", "z") false Tests whether the string "xyz" starts with "z"

=STARTSWITH(@"Property 1", "abc") true Tests whether the shape data property @"Property 1" ("abcdef") starts with the string "abc"

String Manipulation

&

Text concatenation. Combines two strings together into a single string. For example "Lucid" & "chart" produces the string "Lucidchart".

Examples

="A" & "B" "AB"

=@A & "C" ??

="D" & @B & @C ??

CONCATENATE

Creates a new string by combining all specified strings into a single string

Syntax

=CONCATENATE(string1, string2, ..., stringN)

Arguments
Argument Type Description
string String The strings to combine
Examples

=CONCATENATE("ABC", "DEF", 'GHI") "ABCDEFGHI" Combines the strings "ABC", "DEF", and "GHI" into a single string

=CONCATENATE("Hello there, ", @Name, "!") "Hello there, John!" Combines the string "Hello there, ", the shape data property "Name", and the string "!"

=CONCATENATE("A", 1, true) "A1true" Combines the string "A", the number 1, and the boolean value true. Because the Lucid formula system automatically converts value to the correct type, it produces a string version of each value and then creates a string

=CONCATENATE("", "A", "") "A" Combines the strings "" (blank), "A", and "" (blank)

FIND

Returns the position in the specified string of a substring, starting at the specified start position.

Syntax

=FIND(needle, haystack, start)

Arguments
Argument Type Description
needle String The string to find
haystack String The string to search in
start Number (Optional) The index in the haystack to start searching. Defaults to 1

Start is 1-based (i.e. an index of 1 is used for the first character). Negative indexes are allowed and index from the end of the string (i.e. an index of -1 gets the last character).

Examples

=FIND("ABC", "ABCDEF") 1 Finds the string "ABC" in the string "ABCDEF" (starting at character 1, because the index was not specified). Since the string starts with "ABC", returns 1

=FIND("ABC", "ABCDEF", 2) None Finds the string "ABC" in the string "ABCDEF" starting at character 2. The string "ABC" is not found starting at character 2, because the find function is searching within "BCDEF"

=FIND("XYZ", "ABCDEF") None Finds the string "XYZ" in the string "ABCDEF" (starting at character 1, because the index was not specified). The string "XYZ" is not found in the string, so this returns None

=FIND("X","XXXXXX") 1 Finds the string "X" in the string "XXXXXX" (starting at character 1, because the index was not specified). The string "X" is found multiple times in the string, so the first match is returned (1)

=FIND("Z","ABCDEFGHIJKLMNOPQRSTUVWXYZ") 26 Finds the string "Z" in the string "ABCDEFGHIJKLMNOPQRSTUVWXYZ" (starting at character 1, because the index was not specified)

=FIND("Y", "XYZXYZ", -3) 4 Finds the string "Y" in the string "XYZXYZ" starting at the 3rd character from the end

LEFT

Returns the specified number of characters from the start of the string.

Syntax

=LEFT(string, count)

Arguments
Argument Type Description
string String The string to get characters from
count Number The number of characters to get
Examples

=LEFT("ABCDEF", 2) "AB" Gets the first 2 characters from the string "ABCDEF"

=LEFT("GHIJKL", 1) "G" Gets the first character from the string "GHIJKL"

=LEFT("MNOPQR", 0") #ERROR! Gets the first 0 characters from the string "MNOPQR" (returns an error because the number of characters must be greater than or equal to 1

LEN

Returns the length of the string.

Syntax

=LEN(string)

Arguments
Argument Type Description
string String The string to find the length of
Examples

=LEN("Hello World!") 12

=LEN(@"Property 1") 7 Returns the length of the string property called "Property 1"

LOWER

Returns the specified string converted to lower-case.

Syntax

=LOWER(string)

Arguments
Argument Type Description
string String The string to convert
Examples

=LOWER("ABCDE") "abcde"

REGEX_MATCHALL

Returns all matches in the specified string found by the supplied regex.

Syntax

=REGEX_MATCHALL(regex, string)

Arguments
Argument Type Description
regex String The regex to use when looking for matches
string String The string to look for matches in
Examples

=REGEX_MATCHALL("(o(\s|r))", "Hello world") ["o ", "or"]

REGEX_REPLACE

Returns a new string created by replacing all matches in the specified string using the regex and a replacement string.

Syntax

=REGEX_REPLACE(regex, string, replacement)

Arguments
Argument Type Description
regex String The regex to use when looking for matches
string String The string to look for matches in
replacement String The string to replace all found matches with
Examples

=REGEX_REPLACE("b.?", "abc", "ZZZ") "aZZZ"

REGEX_TEST

Returns true if the specified string is matched by the supplied regex.

Syntax

=REGEX_TEST(regex, string)

Arguments
Argument Type Description
regex String The regex to use when looking for matches
string String The string to look for matches in
Examples

=REGEX_TEST("b.?", "abc") true

=REGEX_TEST("x+", "abc") false

REPLACE

Splices a string into the specified string, replacing the characters in the original string from a range of characters.

Syntax

=REPLACE(string, start, count, replacement)

Arguments
Argument Type Description
string String The specified string
start Number The index of the specified string in which to start the replacement
count Number The number of characters to replace
replacement String The string to splice into the specified string

Start is 1-based (i.e. an index of 1 is used for the first character).

Examples

=REPLACE("Hello!", 6, 0, " world") "Hello world!" At the start index of 6, between "o" and "!", insert the string " world"

=REPLACE("Hello world!", 7, 6, "there") "Hello there" At the start index of 7, between " " and "w", replace the next 6 characters with the string "there"

Returns the specified number of characters from the end of the string.

Syntax

=RIGHT(string, count)

Arguments
Argument Type Description
string String The string to get characters from
count Number The number of characters to get
Examples

=RIGHT("ABCDEF", 2) "EF" Gets the last 2 characters from the string "ABCDEF"

=RIGHT("GHIJKL", 1) "L" Gets the last character from the string "GHIJKL"

=RIGHT("MNOPQR", 0") #ERROR! Gets the last 0 characters from the string "MNOPQR" (returns an error because the number of characters must be greater than or equal to 1

Returns the position of the specified search string, starting at an optional start position. Start index can be negative. Allows the wildcard characters ? (to match any single character) and * (to match one or more characters). A ~ before the ? or * character (e.g. "?" or "*") matches the specific character "?" or "*".

Syntax

=SEARCH(needle, haystack, start)

Arguments
Argument Type Description
needle String The string to find. The string can contain the wildcard characters ? or *
haystack String The string to search in
start Number (Optional) The index in the haystack to start searching. Defaults to 1

Start is 1-based (i.e. an index of 1 is used for the first character). Negative indexes are allowed and index from the end of the string (i.e. an index of -1 gets the last character).

Examples

=SEARCH("A?C", "ABCDEF") 1 Finds the string "A?C" ("A", followed by any character, followed by "C") in the string "ABCDEF" (starting at character 1, because the index was not specified). Since the string starts with "ABC", returns 1

=SEARCH("A?C", "ABCDEF", 2) None Finds the string "A?C" ("A", followed by any character, followed by "C") in the string "ABCDEF" starting at character 2. The string "A?C" is not found starting at character 2, because the find function is searching within "BCDEF"

=SEARCH("XYZ", "ABCDEF") None Finds the string "XYZ" in the string "ABCDEF" (starting at character 1, because the index was not specified). The string "XYZ" is not found in the string, so this returns None

=SEARCH("X*X","XXXXXX") 1 Finds the string "XX" ("X" followed by one or more characters, followed by "X") in the string "XXXXXX" (starting at character 1, because the index was not specified). The string "XX" is found multiple times in the string, so the first match is returned (1)

=SEARCH("Z","ABCDEFGHIJKLMNOPQRSTUVWXYZ") 26 Finds the string "Z" in the string "ABCDEFGHIJKLMNOPQRSTUVWXYZ" (starting at character 1, because the index was not specified).

=SEARCH("Y", "XYZXYZ", -3) 5 Finds the string "Y" in the string "XYZXYZ" starting at the 3rd character from the end

SPLIT

Splits the specified string into an array at the specified separator.

Syntax

=SPLIT(string, separator)

Arguments
Argument Type Description
string String The string to split into an array
separator String Character at which to split the string
Examples

=SPLIT("A_B_C_D", "_") ["A", "B", "C", "D"] Splits the string "A_B_C_D" at every underscore found

=SPLIT("Hello", "") ["H", "e", "l", "l", "o"] Splits the string "Hello" at every character

SUBSTITUTE

Replaces a string with another string in a specified string. If an instance number is specified, only substitutes the instance-th match.

Syntax

=SUBSTITUTE(string, old, new, instance)

Arguments
Argument Type Description
string String The string to do substitutions within
old String The existing string to replace
new String The new string to use during replacing
instance Number (Optional) Instance found in which substitution occurs
Examples

=SUBSTITUTE("ABACADAE", "A", "X") "XBXCXDXE" Replaces every character "A" with the character "X"

=SUBSTITUTE("ABACADAE", "A", "X", 3) "ABACXDAE Replaces the 3rd instance of the character "A" with the character "X"

SUBSTRING / MID

Returns the specified number of characters from a string, starting at the specified start position.

Syntax

=SUBSTRING(string, start, count)

=MID(string, start, count)

Arguments
Argument Type Description
string String The string to get characters from
start Number The starting index in the string
count Number The number of characters to return

Index is 1-based (i.e. an index of 1 is used for the first character). Negative indexes are allowed and index from the end of the string (i.e. an index of -1 gets the last character).

Examples

=SUBSTRING("ABC", 1, 1) "A" Gets 1 character from the string "ABC", starting at index 1 ("A")

=SUBSTRING("DEF", 2, 2) "EF" Gets 2 characters from the string "DEF", starting at index 2 ("E")

=MID("ABCDEF", 3, 0) "" Gets 0 characters from the string "ABCDEF", starting at index 3 ("C")

=SUBSTRING("GHIJKL", -3, 3) "JKL" Gets 3 characters from the string "GHIJKL", starting at index -3 (3 characters from the end of the string, starting at "J")

=SUBSTRING("GHIJKL", 0, 3) #ERROR! Gets 3 characters from the string "GHIJKL", starting at index 0 (0 is not a valid index)

=MID("GHIJKL", 10, 1) #ERROR! Gets 1 character from the string "GHIJKL", starting at index 10 (an invalid index because it is longer than the string)

=SUBSTRING("GHIJKL", -10, 1) #ERROR! Gets 1 character from the string "GHIJKL", starting at index -10 (an invalid index because it is longer than the string)

TRIM

Returns a string where all whitespace characters (spaces, tabs, newlines, etc.) have been removed from both the start and end of the specified string.

Syntax

=TRIM(string)

Arguments
Argument Type Description
string String The string to trim whitespace characters from
Examples

=TRIM("ABC ") "ABC" Removes the trailing whitespace characters from the string

=TRIM(" DEF") "DEF" Removes the leading whitespace characters from the string

=TRIM(" GHI ") "GHI" Removes leading and trailing whitespace characters from the string

=TRIM("Hello World") "Hello World" As there are no whitespace characters at the beginning or end of the string, the string is returned unchanged.

TRUNCATE

Returns the string truncated to the specified number of characters, with an ellipsis added at the end if truncation occurred.

Syntax

=TRUNCATE(string, length)

Arguments
Argument Type Description
string String The string to truncate
length Number Desired length
Examples

=TRUNCATE("A VERY LONG TEXT", 9) "A VERY LO…" The text is truncated to the specified number of characters with '…' added at the end

=TRUNCATE("MEDIUM", 7) "MEDIUM" The text is not truncated because the original length is less than the desired length

=TRUNCATE("TEXT", 4) "TEXT" If the desired length is the same as the original length the text is not truncated

UPPER

Returns the specified string converted to upper-case.

Syntax

=UPPER(string)

Arguments
Argument Type Description
string String The string to convert
Examples

=UPPER("abcde") "ABCDE"

=UPPER("hello World!") "HELLO WORLD!"

Number Manipulation

ABS

Calculates the absolute value of the specified number.

Syntax

=ABS(value)

Arguments
Argument Type Description
value Number The value to calculate the absolute value of
Examples

=ABS(4.5) 4.5 Calculates the absolute value of 4.5

=ABS(-1) 1 Calculates the absolute value of -1

=ABS(@"Property 1") 123 Calculates the absolute value of the shape data property "Property 1", which has the value -123

ASPERCENT

Formats a number as a percentage. The percentage is calculated as 100 times the number; for example, 0.25 will be formatted as 25%.

Syntax

=ASPERCENT(value)

Arguments
Argument Type Description
value Number The number to format as a percentage
Examples

=ASPERCENT(0.5) 50% Formats the value 0.5 as a percentage

=ASPERCENT(100) 10000% Formats the value 100 as a percentage

CEIL

Rounds up (to a lesser absolute value) to the nearest multiple of the specified factor. Defaults to a factor of 1.

Syntax

=CEIL(value, factor)

Arguments
Argument Type Description
value Number The value to round up
factor Number (Optional) Specified factor
Examples

=CEIL(3.14) 4 Rounds the value 3.14 up to the nearest multiple of 1

=CEIL(10, 4) 12 Rounds the value 10 up to the nearest multiple of 4

=CEIL(-10, 4) -12 Rounds the value -10 up to the nearest multiple of 4

CLAMP

Limits a value to a specified minimum and maximum value range. If the specified value is below the minimum value or above the maximum value, the minimum value or maximum value, respectively, is returned.

Syntax

=CLAMP(value, min, max)

Arguments
Argument Type Description
value Number The value to clamp
min Number The minimum value
max Number The maximum value
Examples

=CLAMP(3.14159, 0, 10) 3.14159 Restricts the value 3.14159 to the range 0 - 10

=CLAMP(-3.14159, 0, 10) 0 Restricts the value -3.14159 to the range 0 - 10

=CLAMP(3.14159, 0, 1) 1 Restricts the value 3.14159 to the range 0 - 1

=CLAMP(3, 1.5, 2.5) 2.5 Restricts the value 3 to the range 1.5 - 2.5

=CLAMP(1, 1.5, 2.5) 1.5 Restricts the value 1 to the range 1.5 - 2.5

=CLAMP(2, 1.5, 2.5) 2 Restricts the value 2 to the range 1.5 - 2.5

EXP

Calculates the value of Euler's constant (e) raised to the specified value. Equivalent to evalue.

Syntax

=EXP(value)

Arguments
Argument Type Description
value Number The value used as the exponent for e
Examples

=EXP(4.5) 90.01713130052181 Calculates the value of Euler's constant raised to 4.5

=EXP(-1) 0.36787944117144233 Calculates the value of Euler's constant raised to -1

=EXP(@"Property 1") 0.2922925776808594 Calculates the value of Euler's constant raised to the value of the shape data property "Property 1", which has the value -123

FLOOR

Rounds down (to a lesser absolute value) to the nearest multiple of the specified factor. Defaults to a factor of 1.

Syntax

=FLOOR(value, factor)

Arguments
Argument Type Description
value Number The value to round down
factor Number (Optional) Specified factor
Examples

=FLOOR(3.14) 3 Rounds the value 3.14 down to the nearest multiple of 1

=FLOOR(10, 4) 8 Rounds the value 10 down to the nearest multiple of 4

=FLOOR(-10, 4) -8 Rounds the value -10 down to the nearest multiple of 4

LERP / INTERPOLATE

Calculates the linearly interpolated value between two numbers, with a specified proportion.

Syntax

=LERP(value, a, b)

=INTERPOLATE(value, a, b)

The linear interpolation result is calculated as: (b - a) * value + a

Arguments
Argument Type Description
value Number The interpolation value. Value must be between 0 and 1 (values outside of the range will be constrained to that range).
a Number The first value to interpolate between.
b Number The second value to interpolate between.
Examples

=LERP(0.5, 0, 10) 5 Interpolates between 0 and 10 at a proportion of 0.5

=LERP(0, 0, 10) 0 Interpolates between 0 and 10 at a proportion of 0

=INTERPOLATE(1, 0, 10) 10 Interpolates between 0 and 10 at a proportion of 1

=LERP(0.5, -2, 6) 2 Interpolates between -2 and 6 at a proportion of 0.5

LN

Calculates the natural log of the specified number

Syntax

=LN(value)

Arguments
Argument Type Description
value Number The value used to calculate the natural log. Must be greater than 0.
Examples

=LN(4.5) 1.5040773967762742 Calculates the natural log of 4.5

=LN(E) 1 Calculates the natural log of Euler's constant, e

=LN(-1) #ERROR! Calculates the natural log of -1

=LN(@"Property 1") 0.2070141693843261 Calculates the natural log of the shape data property "Property 1", which has the value 1.23

LOG / LOG10

Calculates the base 10 logarithm of the specified number

Syntax

=LOG(value)

=LOG10(value)

Arguments
Argument Type Description
value Number The value used to calculate the base 10 logarithm. Must be greater than 0.
Examples

=LOG(4.5) 0.6532125137753437 Calculates the base 10 log of 4.5

=LOG(10) 1 Calculates the base 10 log of 10

=LOG10(-1) #ERROR! Calculates the base 10 log of -1

=LOG(@"Property 1") 0.08990511143939792 Calculates the base 10 log of the shape data property "Property 1", which has the value 1.23

MOD / MODULUS

Calculates the modulus of a specified number with a specified divisor. The resulting value is the remainder after division of the value by the divisor.

Syntax

=MOD(value, divisor)

=MODULUS(value, divisor)

The sign of the result matches the sign of the value; if a negative value is supplied for value, the result is a negative value. The value and divisor can both be non-integers.

Arguments
Argument Type Description
value Number The value to modulo
divisor Number The divisor of the modulo operation. Cannot be 0.
Examples

=MOD(123, 2) 1 Calculates 123 modulo 2 (the remainder of 123 divided by 2)

=MOD(3.14159, 2) 1.14159 Calculates 3.14159 modulo 2

=MODULUS(-2.178, 1.2) -0.318 Calculates -2.718 modulo 2

=MOD(5, 0) #ERROR! Calculates 5 modulo 0

POW / POWER

Calculates the value of the specified number raised to a given exponent

Syntax

=POW(base, exponent)

=POWER(base, exponent)

Arguments
Argument Type Description
base Number The base to be raised to the exponent power
exponent Number The exponent
Examples

=POW(3.14159, 3) 31.006276680299816 Raises the value 3.14159 to the 3rd power

=POW(3.14159, 2) 9.869604401089358 Raises the value 3.14159 to the 2nd power

=POWER(-2.718, 0) 1 Raises the value -2.718 to the 0th power

=POW(4, 0.5) 2 Raises the value 4 to the 0.5 power

=POW(4, -0.5) 0.5 Raises the value 4 to the -0.5 power

PRODUCT

Calculates the product of a list of numbers or arrays of numbers.

NOTE: If a complex array is provided, like children.children, this function will flatten the array to calculate.

Syntax

=PRODUCT(value1, value2, ... valueN)

Arguments
Argument Type Description
value Number or Array The values used to calculate the product. Allows either numbers or arrays, which can themselves contain numbers or arrays.
Examples

=PRODUCT(1, 2, 3, 4, 5, 6, 7) 5040 Calculates the product of 1, 2, 3, 4, 5, 6, and 7, equivalent to: 1 * 2 * 3 * 4 * 5 * 6 * 7

=PRODUCT(children."Property 1") 2688 Calculates the product of the shape's children's data property value "Property 1" (1, 4, 2, 6, 7, 8)

=PRODUCT(children.children."Property 2") 2880 Calculates the product of the shape's children's children's data property value "Property 2" (12, 15, 16)

ROUND

Rounds a number to specified number of digits.

Syntax

=ROUND(value, digits)

Arguments
Argument Type Description
value Number The value to round
digits Number (Optional) The number of digits to round the value, defaults to 0
Examples

=ROUND(3.14159, 3) 3.142 Rounds the value 3.14159 to 3 digits

=ROUND(3.14159, 2) 3.14 Rounds the value 3.14159 to 2 digits

=ROUND(-2.718) -3 Rounds the value -2.718 to 0 digits

=ROUND(5, 3) 5 Rounds the value 5 to 3 digits

ROUNDDOWN

Rounds a number down (to a lesser value) to the specified number of digits.

Syntax

=ROUNDDOWN(value, digits)

Arguments
Argument Type Description
value Number The value to round
digits Number (Optional) The number of digits to round the value, defaults to 0
Examples

=ROUNDDOWN(2.5) 2 Rounds the value 2.5 to 1 digit

=ROUNDDOWN(-3.14159, 2) -3.15 Rounds the value 3.14159 to 2 digits

=ROUNDDOWN(8192, -2) 8100 Rounds the value -8192 to 2 digits

ROUNDUP

Rounds a number up (to a greater value) to the specified number of digits.

Syntax

=ROUNDUP(value, digits)

Arguments
Argument Type Description
value Number The value to round
digits Number (Optional) The number of digits to round the value, defaults to 0
Examples

=ROUNDUP(2.5) 3 Rounds the value 2.5 to 1 digit

=ROUNDUP(3.14159, 2) 3.15 Rounds the value 3.14159 to 2 digits

=ROUNDUP(-8192, -2) -8100 Rounds the value -8192 to 2 digits

SIGN

Calculates the sign of the specified number as -1, 0, or 1, depending on whether the specified number is negative, zero, or positive, respectively

Syntax

=SIGN(value)

Arguments
Argument Type Description
value Number The value to calculate sign
Examples

=SIGN(3.1459) 1 Calculates the sign of the value 3.14159

=SIGN(-2) -1 Calculates the sign of the value -2

=SIGN(0) 0 Calculates the sign of the value 0

SQRT

Returns the square root of a number.

Syntax

=SQUAREROOT(value)

=SQRT(value)

Arguments
Argument Type Description
value Number The value used to calculate the square root
Examples

=SQRT(16) 4 Calculates the square root of 16

=SQRT(PI) 1.7724538509055159 Calculates the square root of PI

=SQUAREROOT(4) 2 Calculates the square root of 4

=SQRT(COUNT(children)) 2.449489742783178 Calculates the square root of the number of children of the current shape (6)

TRUNC

Calculates the value of the specified number with its fractional component removed. The TRUNC function does not perform any rounding.

Syntax

=TRUNC(value)

Arguments
Argument Type Description
value Number The value to truncate
Examples

=TRUNC(3.1459) 3 Truncates the value 3.14159

=TRUNC(1.9) 1 Truncates the value 1.9

=TRUNC(-2.1) -2 Truncates the value -2.1

=TRUNC(-2.9) -2 Truncates the value -2.9

=TRUNC(2) 2 Truncates the value 2

=TRUNC(0) 0 Truncates the value 0

Arrays

ARRAY

Creates an array of all supplied values

Syntax

=ARRAY(value1, value2, ...)

=ARRAY()

Arguments
Argument Type Description
value Any The value for the specified item, values can be different types
Examples

=ARRAY("A", 2, true) ["A", 2, true] Creates an array consisting of the values "A", 2, and true

=ARRAY() [] Creates an empty array

=ARRAY(1, ARRAY(2, 3), 4) [1, [2, 3], 4] Creates an array consisting of the value 1, an array containing 2 and 3, and the value 4

FLATARRAY / FLATTEN

Creates an array of all supplied values, flattening any arrays so that the array is a single array of values.

Syntax

=FLATARRAY(value1, value2, ...)

=FLATTEN(value1, value2, ...)

Arguments
Argument Type Description
value Any The value for the specified item, values can be different types
Examples

=FLATARRAY(123) [123] Creates a flattened array from the value 123

=FLATARRAY("A", 2, true) ["A", 2, true] Creates a flattened array from the values "A", 2, and true

=FLATTEN(1, ARRAY(2, 3), 4) [1, 2, 3, 4] Creates a flattened array from the value 1, an array containing 2 and 3, and the value 4. Because the array values are flattened, the final array consists of all elements (without arrays): 1, 2, 3, 4.

=FLATTEN(1, ARRAY(2, ARRAY(3, 4))) [1, 2, 3, 4] Creates a flattened array from the value 1, an array containing 2 and and array containing the values 3 and 4. Because the array values are flattened, the final array consists of all elements (without arrays): 1, 2, 3, 4.

RANGE

Creates an array of numbers with values ranging from the start number to the end number, by repetitively adding the step value to calculate values.

NOTE: Range results can only result in arrays of up to 1,000 entries; if the start, end and step values would produce an array with more than 1,000 entries, the function will return an error.

Syntax

=RANGE(start, end, step)

=RANGE(start, end)

Arguments
Argument Type Description
start Number The starting value for the range
end Number The ending value for the range
step Number (Optional) The step to use to calculate the subsequent numbers for the range, defaults to 1
Examples

=RANGE(1, 10) [1, 2, 3, 4, 5, 6, 7, 8, 9, 10] Creates an array of numbers from 1 to 10, with a step of 1 (using the default step)

=RANGE(1, 10, 2) [1, 3, 5, 7, 9] Creates an array of numbers from 1 to 10, with a step of 2

=RANGE(0, -5, -1) [0, -1, -2, -3, -5] Creates a range of numbers from 0 to -10, with a step of -1

=RANGE(0, -10, 1) #ERROR! Attempts to create a range from 0 to -10 with a step of 1, but errors because it would not reach -10 by adding 1

Dates

DATE

Creates a date from the specified values.

Syntax

=DATE(year)

=DATE(year, month)

=DATE(year, month, day)

=DATE(year, month, day, hours)

=DATE(year, month, day, hours, minutes)

=DATE(year, month, day, hours, minutes, seconds)

=DATE(year, month, day, hours, minutes, seconds, milliseconds)

=DATE(year, month, day, hours, minutes, seconds, milliseconds, offset)

Arguments
Argument Type Description
year Number The year for the new date
month Number (Optional) The month for the new date, defaults to 1 (January)
day Number (Optional) The day for the new date, defaults to 1
hours Number (Optional) The hours for the new date, defaults to 0 (midnight)
minutes Number (Optional) The minutes for the new date, defaults to 0
seconds Number (Optional) The seconds for the new date, defaults to 0
milliseconds Number (Optional) The milliseconds for the new date, defaults to 0
offset Number (Optional) The timezone offset for the new date, in minutes, defaults to the browser's current timezone offset
Examples

In the following examples, the current browser has a timezone offset of -7 hours. For more information about offsets see UTC Offset.

=DATE(2020) 2020-01-01 00:00:00.000 Create a date with the year 2020, and defaults for all other values with the current browser timezone

=DATE(2020, 2) 2020-02-01 00:00:00.000 Create a date with the year 2020, the month 2, and defaults for all other values with the current browser timezone

=DATE(2020, 2, 3) 2020-02-03 00:00:00.000 Create a date with the year 2020, the month 2, the day 3, and defaults for all other values with the current browser timezone

=DATE(2020, 2, 3, 4) 2020-02-03 04:00:00.000 Create a date with the year 2020, the month 2, the day 3, the hours 4, and defaults for all other values with the current browser timezone

=DATE(2020, 2, 3, 4, 5) 2020-02-03 04:05:00.000 Create a date with the year 2020, the month 2, the day 3, the hours 4, the minutes 5, and defaults for all other values with the current browser timezone

=DATE(2020, 2, 3, 4, 5, 6) 2020-02-03 04:05:06.000 Create a date with the year 2020, the month 2, the day 3, the hours 4, the minutes 5, the seconds 6, and defaults for all other values with the current browser timezone

=DATE(2020, 2, 3, 4, 5, 6, 7) 2020-02-03 04:05:06.007 Create a date with the year 2020, the month 2, the day 3, the hours 4, the minutes 5, the seconds 6, the milliseconds 7, with the current browser timezone

=DATE(2020, 2, 3, 4, 5, 6, 7, -720) 2020-02-03 09:05:06.007 Create a date with the year 2020, the month 2, the day 3, the hours 4, the minutes 5, the seconds 6, the milliseconds 7, with the timezone offset of -720 (a -12 hours offset)

DATEADD

Creates a new date by ading an offset to a specified date.

Syntax

=DATEADD(interval, count, date)

Arguments
Argument Type Description
interval String The type of interval to find the difference of
Valid intervals are:
  • YEARS
  • YEAR
  • QUARTERS
  • QUARTER
  • MONTHS
  • MONTH
  • WEEKS
  • WEEK
  • DAYS
  • DAY
  • HOURS
  • HOUR
  • MINUTES
  • MINUTE
  • SECONDS
  • SECOND
  • MILLISECONDS
  • MILLISECOND
count Number The number of intervals to add to the specified date
date Date The date to add intervals to
Examples

=DATEADD("YEAR", 1, DATE(2019, 01, 01)) Jan 1, 2020 12:00 AM Adds one year to the date Jan 1, 2019 12:00 AM

=DATEADD("MONTHS", 3, DATE(2019, 01, 01)) Apr 1, 2019 12:00 AM Adds three months to the date Jan 1, 2019 12:00 AM

DATEDIFF

Calculates the difference between two specified dates in the given interval. If date2 is before date1, then the result will be negative.

Syntax

=DATEDIFF(interval, date1, date2)

Arguments
Argument Type Description
interval String The type of interval to find the difference of
Valid intervals are:
  • YEARS
  • YEAR
  • QUARTERS
  • QUARTER
  • MONTHS
  • MONTH
  • WEEKS
  • WEEK
  • DAYS
  • DAY
  • HOURS
  • HOUR
  • MINUTES
  • MINUTE
  • SECONDS
  • SECOND
  • MILLISECONDS
  • MILLISECOND
date1 Date The starting date
date2 Date The ending date
Examples

=DATEDIFF("YEARS", DATE(2019), DATE(2020)) 1 Gets the number of years from Jan 1, 2019 12:00 AM to Jan 1, 2020 12:00 AM

=DATEDIFF("YEARS", DATE(2021), DATE(2019)) -2 Gets the number of years from Jan 1, 2021 12:00 AM to Jan 1, 2019 12:00 AM

=DATEDIFF("MINUTES", DATE(2020, 1, 2, 3), DATE(2020, 2, 3, 4)) 46140 Gets the number of minutes from Jan 2, 2020 3:00 AM to Feb 3, 2020 4:00 AM

DAY

Gets the day component of the specified date.

Syntax

=DAY(date)

Arguments
Argument Type Description
date Date The date to use for calculation
Examples

=DAY(DATE(2020, 2, 3, 4, 5)) 3 Gets the day component 3 from the specified date Feb 3, 2020 4:05 AM

=DAY(DATE(2020, 4, 5, 6, 7)) 5 Gets the day component 5 from the specified date Apr 5, 2020 6:07 AM

DAYOFWEEK

Calculates the day of the week (1-7) for a specified date where the week starts on Monday.

Syntax

=DAYOFWEEK(date)

Arguments
Argument Type Description
date Date The date to use for calculation
Examples

=DAYOFWEEK(DATE(2020, 2, 3, 4, 5)) 1 Feb 3, 2020 4:05 AM is a Monday

=DAYOFWEEK(DATE(2020, 3, 4, 5, 6)) 3 Mar 4, 2020 5:06 AM is a Wednesday

DAYSAGO

Gets the number of days between the specified date and now.

Syntax

=DAYSAGO(date)

Arguments
Argument Type Description
date Date The date to use for calculation
Examples

The following examples assume the current date is January 2, 2020 at 12:00pm

=DAYSAGO("2019-01-01") 366.7916666666667 Gets the number of days from midnight January 1, 2019 to January 2, 2020 at 12:00pm

=DAYSAGO("2020-01-01") 1.7916666666666667 Gets the number of days from midnight January 1, 2020 to January 2, 2020 at 12:00pm

=DAYSAGO("2020-01-03") -0.20833333333333334 Gets the number of days from midnight January 3, 2020 to January 2, 2020 at 12:00pm

EXCELDATE

Creates a date from a numeric Excel date value assuming the value is stored as the number of days since Dec 30, 1899 (where 1 is 12/31/1899).

Syntax

=EXCELDATE(value)

Arguments
Argument Type Description
value Number The number of days since Dec 30, 1899
Examples

=EXCELDATE(1) Dec 31, 1899 12:00 AM

=EXCELDATE(43800) Dec 31, 2019 12:00 AM

FORMAT_DATE

Returns a string representing the date, formatted according to the given pattern. Pattern formatting is generally the same as supported by Excel.

Syntax

=FORMAT_DATE(date, pattern)

Arguments
Argument Type Description
date Date The date to format
pattern String The pattern by which to format the date
Examples

=FORMAT_DATE(DATE(2019, 12, 25),"MM-dd") 12-25

=FORMAT_DATE(DATE(2020, 2, 3, 4, 5),"YYYY-MM-dd, hh:mm") 2020-02-03, 04:05

HOUR

Gets the hour component of the specified date.

Syntax

=HOUR(date)

Arguments
Argument Type Description
date Date The date to use for calculation
Examples

=HOUR(DATE(2020, 2, 3, 4, 5)) 4 Gets the hour component 4 from the specified date Feb 3, 2020 4:05 AM

=HOUR(DATE(2020, 4, 5, 6, 7)) 6 Gets the hour component 6 from the specified date April 5, 2020 6:07 AM

HOURSAGO

Gets the number of hours between the specified date and now.

Syntax

=HOURSAGO(date)

Arguments
Argument Type Description
date Date The date to use for calculation
Examples

The following examples assume the current date is January 2, 2020 at 12:00pm

=HOURSAGO("2019-01-01") 8803 Gets the number of hours from midnight January 1, 2019 to January 2, 2020 at 12:00pm

=HOURSAGO("2020-01-01") 43 Gets the number of hours from midnight January 1, 2020 to January 2, 2020 at 12:00pm

=HOURSAGO("2020-01-03") -5 Gets the number of hours from midnight January 3, 2020 to January 2, 2020 at 12:00pm

ISODATE

Creates a date from an ISO8601-formatted string. See ISO8601 formatting for more details.

Syntax

=ISODATE(value)

Arguments
Argument Type Description
value String ISO8601-formatted string
Examples

In the following examples, the current browser has a timezone offset of -7 hours. For more information about offsets see UTC Offset.

=ISODATE("2020-01-01T00:00:00+0000") Dec 31, 2019 5:00 PM

=ISODATE("2016-06-01T04:15:00+0000") May 31, 2016 10:15 PM

=ISODATE("2020-02-03") Feb 3, 2020 12:00 AM

=ISODATE("2020-01") Jan 1, 2020 12:00 AM

=ISODATE("2021-W01-1") Dec 30, 2019 12:00 AM

MINUTE

Gets the minute component of the specified date.

Syntax

=MINUTE(date)

Arguments
Argument Type Description
date Date The date to use for calculation
Examples

=MINUTE(DATE(2020, 2, 3, 4, 5)) 5 Gets the minute component 5 from the specified date Feb 3, 2020 4:05 AM

=MINUTE(DATE(2020, 4, 5, 6, 7)) 7 Gets the minute component 7 from the specified date Apr 5, 2020 6:07 AM

MINUTESAGO

Gets the number of minutes between the specified date and now.

Syntax

=MINUTESAGO(date)

Arguments
Argument Type Description
date Date The date to use for calculation
Examples

The following examples assume the current date is January 2, 2020 at 12:00pm

=MINUTESAGO("2019-01-01") 527760 Gets the number of minutes from midnight January 1, 2019 to January 2, 2020 at 12:00pm

=MINUTESAGO("2020-01-01") 2160 Gets the number of minutes from midnight January 1, 2020 to January 2, 2020 at 12:00pm

=MINUTESAGO("2020-01-03") -720 Gets the number of minutes from midnight January 3, 2020 to January 2, 2020 at 12:00pm

MONTH

Gets the month component of the specified date.

Syntax

=MONTH(date)

Arguments
Argument Type Description
date Date The date to use for calculation
Examples

=MONTH(DATE(2020, 2, 3, 4, 5)) 2 Gets the month component 2 from the specified date Feb 3, 2020 4:05 AM

=MONTH(DATE(2020, 4, 5, 6, 7)) 4 Gets the month component 4 from the specified date Apr 5, 2020 6:07 AM

MONTHSAGO

Gets the number of months between the specified date and now.

Syntax

=MONTHSAGO(date)

Arguments
Argument Type Description
date Date The date to use for calculation
Examples

The following examples assume the current date is January 2, 2020 at 12:00pm

=MONTHSAGO("2019-06-01") 7.05729578742284 Gets the number of months from midnight June 1, 2019 to January 2, 2020 at 12:00pm

NETWORKDAYS

Gets the number of working days between a specified beginning and ending date, inclusive. Working days exclude weekends and any dates optionally provided as holidays.

Syntax

=NETWORKDAYS(start, end, holidays)

Arguments
Argument Type Description
start Date The beginning date to use for the calculation
end Date The end date to use for the calculation
holidays Array(Date) (Optional) Dates to exclude from the count of working days
Examples

=NETWORKDAYS("2020-04-01", "2020-04-10") 8 Counts the days Mon-Fri within the range

=NETWORKDAYS("2020-04-01", "2020-04-01") 1 The range is inclusive, so if the start and end are the same day, the result is 1

=NETWORKDAYS("2020-04-07", "2020-04-2") -4 If the end date is before the start date, a negative value is returned

=NETWORKDAYS("2020-04-01 :20:00", "2020-04-10 7:00") 8 If a time is included with the date(s), the time is ignored

=NETWORKDAYS("2020-04-01", "2020-04-15", ["2020-04-07", "2020-04-03", "2020-03-31"]) 9 Holidays are excluded. Holidays outside the interval or on weekends are ignored

NOW

Returns the current date and time. Automatically updates every second.

Syntax

=NOW()

Examples

=NOW Jul 1, 2020 8:00 AM

QUARTER

Gets the calendar quarter for the specified date. For more details see Quarters.

Syntax

=QUARTER(date)

Arguments
Argument Type Description
date Date The date to use for calculation
Examples

=QUARTER(DATE(2020, 2, 3, 4, 5)) 1 Gets the quarter component 1 from the specified date Feb 3, 2020 4:05 AM

=QUARTER(DATE(2020, 4, 5, 6, 7)) 2 Gets the quarter component 2 from the specified date Apr 5, 2020 6:07 AM

SECOND

Gets the second component of the specified date.

Syntax

=SECOND(date)

Arguments
Argument Type Description
date Date The date to use for calculation
Examples

=SECOND(DATE(2020, 2, 3, 4, 5)) 0 Gets the second component 0 from the specified date Feb 3, 2020 4:05:00 AM

=SECOND(DATE(2020, 4, 5, 6, 7, 8)) 8 Gets the second component 8 from the specified date Apr 5, 2020 6:07:08 AM

SECONDSAGO

Gets the number of seconds between the specified date and now.

Syntax

=SECONDSAGO(date)

Arguments
Argument Type Description
date Date The date to use for calculation
Examples

The following examples assume the current date is January 2, 2020 at 12:00pm

=SECONDSAGO("2019-01-01") 31665600 Gets the number of seconds from midnight January 1, 2019 to January 2, 2020 at 12:00pm

=SECONDSAGO("2020-01-01") 129600 Gets the number of seconds from midnight January 1, 2020 to January 2, 2020 at 12:00pm

=SECONDSAGO("2020-01-03") -43200 Gets the number of seconds from midnight January 3, 2020 to January 2, 2020 at 12:00pm

UNIXDATE

Creates a date from the number of seconds since the Unix epoch (January 1, 1970, 00:00:00 UTC).

Syntax

=UNIXDATE(value)

Arguments
Argument Type Description
value Number number of seconds
Examples

=UNIXDATE(1593561600) Jun 30, 2020 6:00 PM

=UNIXDATE(1607136108) Dec 4, 2020 7:41 PM

UTCDATE

Creates a date from the specified values.

Syntax

=UTCDATE(year)

=UTCDATE(year, month)

=UTCDATE(year, month, day)

=UTCDATE(year, month, day, hours)

=UTCDATE(year, month, day, hours, minutes)

=UTCDATE(year, month, day, hours, minutes, seconds)

=UTCDATE(year, month, day, hours, minutes, seconds, milliseconds)

Arguments
Argument Type Description
year Number The year for the new date
month Number (Optional) The month for the new date, defaults to 1 (January)
day Number (Optional) The day for the new date, defaults to 1
hours Number (Optional) The hours for the new date, defaults to 0 (midnight)
minutes Number (Optional) The minutes for the new date, defaults to 0
seconds Number (Optional) The seconds for the new date, defaults to 0
milliseconds Number (Optional) The milliseconds for the new date, defaults to 0
Examples

In the following examples, the current browser has a timezone offset of -7 hours. For more information about offsets see UTC Offset.

=UTCDATE(2020) 2019-12-31 17:00:00.000 Create a date with the year 2020, and defaults for all other values with the current browser timezone

=UTCDATE(2020, 2) 2020-01-31 17:00:00.000 Create a date with the year 2020, the month 2, and defaults for all other values with the current browser timezone

=UTCDATE(2020, 2, 3) 2020-02-02 17:00:00.000 Create a date with the year 2020, the month 2, the day 3, and defaults for all other values with the current browser timezone

=UTCDATE(2020, 2, 3, 4) 2020-02-02 21:00:00.000 Create a date with the year 2020, the month 2, the day 3, the hours 4, and defaults for all other values with the current browser timezone

=UTCDATE(2020, 2, 3, 4, 5) 2020-02-02 21:05:00.000 Create a date with the year 2020, the month 2, the day 3, the hours 4, the minutes 5, and defaults for all other values with the current browser timezone

=UTCDATE(2020, 2, 3, 4, 5, 6) 2020-02-02 21:05:06.000 Create a date with the year 2020, the month 2, the day 3, the hours 4, the minutes 5, the seconds 6, and defaults for all other values with the current browser timezone

=UTCDATE(2020, 2, 3, 4, 5, 6, 7) 2020-02-02 21:05:06.007 Create a date with the year 2020, the month 2, the day 3, the hours 4, the minutes 5, the seconds 6, the milliseconds 7, with the current browser timezone

WEEK

Gets the week component of the specified date. For more information see ISO Week Date.

Syntax

=WEEK(date)

Arguments
Argument Type Description
date Date The date to use for calculation
Examples

=WEEK(DATE(2020, 2, 3, 4, 5)) 6 Gets the week component 6 from the specified date Feb 3, 2020 4:05 AM

=WEEK(DATE(2020, 4, 5, 6, 7)) 14 Gets the week component 14 from the specified date Apr 5, 2020 6:07 AM

WEEKSAGO

Gets the number of weeks between the specified date and now.

Syntax

=WEEKSAGO(date)

Arguments
Argument Type Description
date Date The date to use for calculation
Examples

The following examples assume the current date is January 2, 2020 at 12:00pm

=WEEKSAGO("2019-01-01") 52.398809523809526 Gets the number of weeks from midnight January 1, 2019 to January 2, 2020 at 12:00pm

=WEEKSAGO("2020-01-01") 0.25595238095238093 Gets the number of weeks from midnight January 1, 2020 to January 2, 2020 at 12:00pm

=WEEKSAGO("2020-01-03") -0.02976190476190476 Gets the number of weeks from midnight January 3, 2020 to January 2, 2020 at 12:00pm

WEEKYEAR

Gets the year for the associated ISO week of the specified date. For more information see ISO Week Date.

Syntax

=WEEKYEAR(date)

Arguments
Argument Type Description
date Date The date to use for calculation
Examples

=WEEKYEAR(DATE(2010, 1, 2)) 2009 Gets the week-year component 2009 from the specified date Jan 2, 2010 12:00 AM

=WEEKYEAR(DATE(2020, 4, 5, 6, 7)) 2020 Gets the week-year component 2020 from the specified date Apr 5, 2020 6:07 AM

WORKDAY

Given a start date, finds the date a specified number of working days later. A list of holidays can optionally be provided, indicating the given dates are not working days.

NOTE: While the NETWORKDAYS function calculates a range inclusive of the start date, WORKDAY excludes the start date. For example, WORKDAY("2020-04-01", NETWORKDAYS("2020-04-01", "2020-04-01")) will return 2020-04-02.

Syntax

=WORKDAY(start, workdays, holidays)

Arguments
Argument Type Description
start Date The beginning date to use for the calculation
workdays Number The number of working days to add to the start date
holidays Array(Date) (Optional) Dates to exclude from the count of working days
Examples

=WORKDAY("2020-04-01", 7) 2020-04-10 Returns the date 7 working days (Mon-Fri) later

=WORKDAY("2020-04-08", -5) 2020-04-01 The function accepts negative values and will return a date before the date provided

=WORKDAY("2020-04-01", 3, ["2020-04-03", "2020-03-31", "2020-04-16"]) 2020-04-07 Holidays are excluded. Holidays outside the interval or on weekends are ignored

YEAR

Gets the year component of the specified date.

Syntax

=YEAR(date)

Arguments
Argument Type Description
date Date The date to use for calculation
Examples

=YEAR(DATE(2020, 2, 3, 4, 5)) 2020 Gets the year component 2020 from the specified date Feb 3, 2020 4:05 AM

=YEAR(DATE(2016, 4, 5, 6, 7)) 2016 Gets the year component 2016 from the specified date Apr 5, 2016 6:07 AM

YEARSAGO

Gets the number of years between the specified date and now.

Syntax

=YEARSAGO(date)

Arguments
Argument Type Description
date Date The date to use for calculation
Examples

The following examples assume the current date is January 2, 2020 at 12:00pm

=YEARSAGO("2019-01-01") 1.0048163082437276 Gets the number of years from midnight January 1, 2019 to January 2, 2020 at 12:00pm

=YEARSAGO("2020-01-01") 0.004816308243727595 Gets the number of years from midnight January 1, 2020 to January 2, 2020 at 12:00pm

=YEARSAGO("2020-01-03") -0.0005600358422939068 Gets the number of years from midnight January 3, 2020 to January 2, 2020 at 12:00pm

Objects

GET

Gets the associated value from an object for the specified key.

Syntax

=GET(obj, key)

Arguments
Argument Type Description
obj Object The object to use.
key String The key to use within the object, will be converted to a string type if the value given is a non-string
Examples

In these examples, the shape data property "Object" is an object with the following key-value pairs:

{ "A": 1, "B": 2, "C": 3, "D": 4, "E": 5, }

=GET(@Object, "A") 1 Get the value associated with the key "A" from the object stored in the shape data property named "Object".

=GET(@Object, "D") 4 Get the value associated with the key "D" from the object stored in the shape data property named "Object"

GROUPBY

Creates an object from an array and a key expression which determines the key for each item, and a value expression which determines the value.

Syntax

=GROUPBY(array, keyExpression, valueExpression)

Arguments
Argument Type Description
array String The array to use.
keyExpression Any The expression which determines which key to use for the item in the array.
valueExpression Array The expression which determines which value to use for the item in the array.
Examples

For these examples, the shape data property "Array" is an array with the values:

[ ["James", 1, 10], ["Mary", 2, 20], ["Robert", 3, 30], ["Michael", 4, 40], ]

=GROUPBY(@Array, x => x[1], x => x[2]) {"James": 1, "Mary": 2, "Robert": 3, "Michael": 4} Creates an object from the array values, grouping by the first element in each array, where the value is the second element

=GROUPBY(@Array, this[1], this[3]) {"James": 10, "Mary": 20, "Robert": 30, "Michael": 40} Creates an object from the array values, grouping by the first element in each array, where the value is the second element

HAS

Returns true if the object or reference has a value with the specified key.

Syntax

=HAS(obj, key)

Arguments
Argument Type Description
obj Any The object or reference to test
key String The key to look for
Examples

=HAS(OBJECT("A", 1, "B", 2), "A") true Tests whether an object with the key-value pairs "A" => 1, "B" => 2 has the key "A"

=HAS(OBJECT("A", 1, "B", 2), "C") false Tests whether an object with the key-value pairs "A" => 1, "B" => 2 has the key "C"

=HAS(this, "Property 1) true Tests whether the current shape has a property named "Property 1".

=HAS(this, "Property 2) false Tests whether the current shape has a property named "Property 2".

KEYS

Return the array of strings which will can be referenced using GET() on the parameter. Defaults to THIS if no argument provided.

Syntax

=KEYS(obj)

Arguments
Argument Type Description
obj Any The object or reference to test
Example

=KEYS(OBJECT("A", 1, "B", 2)) ["A", "B"] Returns the keys of the given object

OBJECT

Creates an object that can be queried by key. Arguments are passed in as either an array of key-value pairs or as paired key and value parameters.

Syntax

=OBJECT(key1, value1, key2, value2, ...)

=OBJECT(keyvalues)

Arguments
Argument Type Description
keyN String The key for the key-value pair. If the key is a non-string it will be converted to a string type
valueN Any The value for the specified key. Values can be different types
keyvalues Array An array of key-value pairs. Each element in the array should be an array with two values, the first representing the key and the second representing the value.
Examples

=OBJECT("A", 2) {"A": 1} Creates an object consisting of the key "A" which maps to the value 1

=OBJECT("A", 1, "B", 2) {"A": 1, "B": 2} Creates an object consisting of the key "A" which maps to the value 1, and the key "B" which maps to the value 2.

=OBJECT(ARRAY("A", 1), ARRAY("B", 2)) {"A": 1, "B": 2} Creates an object consisting of the key "A" which maps to the value 1, and the key "B" which maps to the value 2

VALUES

Returns the dereferenced values of all keys available on the specified parameter. Defaults to THIS if no argument provided. (When on a shape data property, VALUES(THIS) always returns at least one error value due to a circular reference.)",

Syntax

=VALUES(obj)

Arguments
Argument Type Description
obj Any The object or reference to test
Examples

=VALUES(OBJECT("A", 1, "B", 2)) [1, 2] Returns the values of the given object

=VALUES() [123, "Hello World", true, #ERROR!] Returns the values of the current object including at least one error value

Smart Containers

CONTAINER

Gets the container that contains the selected item in a Smart Container.

NOTE: Used on items within a Smart Container.

Syntax

=CONTAINER

Examples

=CONTAINER "My Container" Gets the container which contains the current shape

=CONTAINER.CONTENTS [Shape 1, Shape 2, Shape 3, Shape 4] Gets the contents of the current shape's container

CONTENTS

Gets the contents of the selected Smart Container.

Syntax

=CONTENTS

Examples

=CONTENTS [Shape 1, Shape 2, Shape 3, Shape 4] Gets all shapes contained in the Smart Container

=COUNT(CONTENTS) 4 Gets the count of all shapes contained in the Smart Container

=SUM(CONTENTS."Property 1") 13 Calculates the sum of all shapes contained in the Smart Container's shape data property "Property 1" (1, 4, 2, 6)

Trigonometric

ACOS

Calculates the inverse cosine of the specified number (arccosine), in radians.

The ACOS function calculates the angle for a given cosine value. The value argument must be in the range (-1, 1); values outside this range will return #ERROR!

Syntax

=ACOS(value)

Arguments
Argument Type Description
value Number The cosine value, in the range (-1, 1)
Examples

=ACOS(1) 0 Calculates the arccosine of 1

=ACOS(0) 1.57079632679489661923 Calculates the arccosine of 0

=ACOS(@"Property 1") 1.33871864393218344181 Calculates the arccosine of the shape data property @"Property 1" (0.23)

ACOSH

Calculates the inverse hyperbolic cosine of the specified number.

Syntax

=ACOSH(value)

Arguments
Argument Type Description
value Number The value which must be greater than 1
Examples

=ACOSH(1) 0 Calculates the inverse hyperbolic cosine of 1

=ACOSH(0) #ERROR! Calculates the inverse hyperbolic cosine of 0

=ACOSH(@"Property 1") 0.66586352915655483273 Calculates the inverse hyperbolic cosine of the shape data property @"Property 1" (1.23)

ACOT

Calculates the inverse cotangent of the specified number (arccotangent), in radians.

The ACOT function calculates the angle for a given cotangent value.

Syntax

=ACOT(value)

Arguments
Argument Type Description
value Number The cotangent value
Examples

=ACOT(1) 0.78539816339744830962 Calculates the arccotangent of 1

=ACOT(0) 1.57079632679489661923 Calculates the arccotangent of 0

=ACOT(@"Property 1") 1.34472793880101271339 Calculates the arccotangent of the shape data property @"Property 1" (0.23)

ACOTH

Calculates the inverse hyperbolic cotangent of the specified number.

Syntax

=ACOTH(value)

Arguments
Argument Type Description
value Number The value, must not be in the range (-1, 1)
Examples

=ACOTH(2) 0.5493061443340548457 Calculates the inverse hyperbolic cotangent of 2

=ACOTH(0) #ERROR! Calculates the inverse hyperbolic cotangent of 0

=ACOTH(@"Property 1") 1.13583877776548453391 Calculates the inverse hyperbolic cotangent of the shape data property @"Property 1" (1.23)

ASIN

Calculates the inverse sine of the specified number (arcsine), in radians.

The ASIN function calculates the angle for a given sine value.

Syntax

=ASIN(value)

Arguments
Argument Type Description
value Number The sine value
Examples

=ASIN(1) 1.57079632679489661923 Calculates the arcsine of 1

=ASIN(0) 0 Calculates the arcsine of 0

=ASIN(@"Property 1") 0.23207768286271317743 Calculates the arcsine of the shape data property @"Property 1" (0.23)

ASINH

Calculates the inverse hyperbolic sine of the specified number.

Syntax

=ASINH(value)

Arguments
Argument Type Description
value Number The value
Examples

=ASINH(1) 0.88137358701954302523 Calculates the inverse hyperbolic sine of 2

=ASINH(0) 0 Calculates the inverse hyperbolic sine of 0

=ASINH(@"Property 1") 1.03503789619230760411 Calculates the inverse hyperbolic sine of the shape data property @"Property 1" (1.23)

ATAN

Calculates the inverse tangent of the specified number (arctangent), in radians.

The ATAN function calculates the angle for a given tangent value.

Syntax

=ATAN(value)

Arguments
Argument Type Description
value Number The tangent value
Examples

=ATAN(1) 0.78539816339744830962 Calculates the arctangent of 1

=ATAN(0) 0 Calculates the arctangent of 0

=ATAN(@"Property 1") 0.22606838799388390584 Calculates the arctangent of the shape data property @"Property 1" (0.23)

ATAN2

Calculates the inverse tangent of the specified x- and y-coordinates (arctangent).

The arctangent is calculated as the angle (in radians) from the x-axis to a line containing the origin (0, 0) and the point (x, y).

Syntax

=ATAN2(x, y)

Arguments
Argument Type Description
x Number The x-coordinate
y Number The y-coordinate
Examples

=ATAN2(0, 1) 1.5707963267948966 Calculates the angle from the x-axis to a line from the origin to (0, 1)

=ATAN2(1, 0) 0 Calculates the angle from the x-axis to a line from the origin to (1, 0)

=ATAN2(@"Property 1", @"Property 2") -1.3956853388722992 Calculates the angle from the x-axis to a line from the origin to (@"Property 1", @"Property 2"). (0.23, -1.3)

ATANH

Calculates the inverse hyperbolic tangent of the specified number.

Syntax

=ATANH(value)

Arguments
Argument Type Description
value Number The value, in the range (-1, 1)
Examples

=ATANH(0.5) 0.5493061443340548 Calculates the inverse hyperbolic tangent of 0.5

=ATANH(0) 0 Calculates the inverse hyperbolic tangent of 0

=ATANH(@"Property 1") 0.2341894667593668 Calculates the inverse hyperbolic tangent of the shape data property @"Property 1" (0.23)

COS / COSINE

Calculates the cosine, using the specified angle in radians.

Syntax

=COS(angle)

=COSINE(angle)

Arguments
Argument Type Description
angle Number The angle in radians
Examples

=COS(PI) -1 Calculates the cosine of PI

=COSINE(0) 1 Calculates the cosine of 0

=COS(@"Property 1") -0.88796890669185542898 Calculates the cosine of the shape data property @"Property 1" (123)

COSH

Calculates the hyperbolic cosine of the specified angle, in radians.

Syntax

=COSH(angle)

Arguments
Argument Type Description
angle Number The angle in radians
Examples

=COSH(1) 1.5430806348152437 Calculates the hyperbolic cosine of 1

=COSH(0) 1 Calculates the hyperbolic cosine of 0

=COSH(@"Property 1") 1.8567610569852664 Calculates the hyperbolic cosine of the shape data property @"Property 1" (1.23)

COT

Calculates the cotangent, using the specified angle in radians.

Cotangent is the reciprocal of tangent, equal to 1 / TAN(angle). Returns #ERROR! for 0.

Syntax

=COT(angle)

Arguments
Argument Type Description
angle Number The angle in radians
Examples

=COT(PI / 4) 1 Calculates the cotangent of PI / 4

=COT(0) #ERROR! Calculates the cotangent of 0

=COT(@"Property 1") 1.93077226998301700942 Calculates the cotangent of the shape data property @"Property 1" (123)

DEGREES

Converts the specified angle from radians to degrees.

Syntax

=DEGREES(angle)

Arguments
Argument Type Description
angle Number The angle in radians
Examples

=DEGREES(PI) 180 Converts the angle PI in radians to degrees

=DEGREES(0) 0 Converts the angle 0 in radians to degrees

=DEGREES(@"Property 1") 7047.54551219769746366041 Converts the angle in the shape data property "Property 1" (123) in radians to degrees

RADIANS

Converts the specified angle from degrees to radians.

Syntax

=RADIANS(angle)

Arguments
Argument Type Description
angle Number The angle in degrees
Examples

=RADIANS(180) 3.1415926535 Converts the angle PI in degrees to radians

=RADIANS(0) 0 Converts the angle 0 in degrees to radians

=RADIANS(@"Property 1") 2.1467048313225 Converts the angle in the shape data property "Property 1" (123) in degrees to radians

SIN / SINE

Calculates the sine, using the specified angle in radians.

Syntax

=SIN(angle)

=SINE(angle)

Arguments
Argument Type Description
angle Number The angle in radians
Examples

=SIN(PI) 0 Calculates the sine of PI

=SINE(0) 1 Calculates the sine of 0

=SIN(@"Property 1") -0.45990349068959125129 Calculates the sine of the shape data property @"Property 1" (123)

SINH

Calculates the hyperbolic sine of the specified angle, in radians.

Syntax

=SINH(angle)

Arguments
Argument Type Description
angle Number The angle in radians
Examples

=SINH(1) 1.1752011936438014 Calculates the hyperbolic sine of 1

=SINH(0) 0 Calculates the hyperbolic sine of 0

=SINH(@"Property 1") 1.5644684793044068 Calculates the hyperbolic sine of the shape data property @"Property 1" (1.23)

TAN / TANGENT

Calculates the tangent, using the specified angle in radians.

Syntax

=TAN(angle)

=TANGENT(angle)

Arguments
Argument Type Description
angle Number The angle in radians
Examples

=TAN(PI / 4) 1 Calculates the tangent of PI / 4

=TANGENT(0) 0 Calculates the tangent of 0

=TAN(@"Property 1") 0.51792747158565518313 Calculates the tangent of the shape data property @"Property 1" (123)

TANH

Calculates the hyperbolic tangent of the specified angle, in radians.

Syntax

=TANH(angle)

Arguments
Argument Type Description
angle Number The angle in radians
Examples

=TANH(1) 0 Calculates the hyperbolic tangent of 1

=TANH(0) 0.7615941559557649 Calculates the hyperbolic tangent of 0

=TANH(@"Property 1") 0.8425793256589296 Calculates the hyperbolic tangent of the shape data property @"Property 1" (1.23)

Color Creation

HEXCOLOR

Creates a new color from the specified hexadecimal color string.

Syntax

=HEXCOLOR(hexstring)

Arguments
Argument Type Description
hexstring String The hex color string, in the form #RRGGBBAA or #RRGGBB
Examples

=HEXCOLOR("#00ff00") #00ff00ff

=HEXCOLOR("#f96b1388") #f96b1388

HSL / HSLA

Creates a new color from the specified hue, saturation, lightness and alpha values.

Syntax

=HSL(hue, saturation, lightness)

=HSLA(hue, saturation, lightness, alpha)

Arguments
Argument Type Description
hue Number The hue value, from 0 to 255
saturation Number The saturation value, from 0 to 255
lightness Number The lightness value, from 0 to 255
alpha Number (Optional) The alpha value, a decimal value from 0 to 1
Examples

=HSLA(0, 1, 0.5, 1) #ff0000ff

=HSLA(0, 1, 1, 0.5) #ffffff7f

HSV / HSVA

Creates a new color from the specified hue, saturation, value and alpha values.

Syntax

=HSV(hue, saturation, value)

=HSVA(hue, saturation, value, alpha)

Arguments
Argument Type Description
hue Number The hue value, from 0 to 255
saturation Number The saturation value, from 0 to 255
value Number The value value, from 0 to 255
alpha Number (Optional) The alpha value, a decimal value from 0 to 1
Examples

=HSVA(0, 0.5, 1, 1) #ff8080ff

=HSVA(110, 0.5, 0.5, 0.5) #4b80407f

RGB / RGBA

Creates a new color from the specified red, green, blue and alpha values.

Syntax

=RGB(red, green, blue)

=RGBA(red, green, blue, alpha)

Arguments
Argument Type Description
red Number The red value, from 0 to 255
green Number The green value, from 0 to 255
blue Number The blue value, from 0 to 255
alpha Number (Optional) The alpha value, a decimal value from 0 to 1
Examples

=RGBA(0, 255, 0, 0.5) #00ff007f

=RGBA(0, 128, 0, 1) #008000ff

Color Values

ALPHA

Gets the alpha channel of the specified color. Returns a decimal from 0 to 1.

Syntax

=ALPHA(color)

Arguments
Argument Type Description
color Color The color
Examples

=ALPHA("#0000ff") 1 Gets the alpha channel for the color represented by the hex color code #0000ff (blue)

=ALPHA(RGB(128, 128, 128, 0.5)) 0.5 Gets the alpha channel for a color having a value of 128 for red, 128 for green, 128 for blue, and 0.5 for alpha

=ALPHA(0) #ERROR! Attempts to get the alpha channel from an integer, which is invalid because it cannot be converted to a color

BLUE

Gets the blue channel of the specified color in the RGB colorspace. Returns an integer from 0 to 255.

For more information about the RGB colorspace, see: https://en.wikipedia.org/wiki/RGB

Syntax

=BLUE(color)

Arguments
Argument Type Description
color Color The color
Examples

=BLUE("#0000ff") 255 Gets the blue channel for the color represented by the hex color code #0000ff (blue)

=BLUE(RGB(128, 128, 128)) 128 Gets the blue channel for a color having a value of 128 for red, 128 for green, and 128 for blue

=BLUE(0) #ERROR! Attempts to get the blue channel from an integer, which is invalid because it cannot be converted to a color

GREEN

Gets the green channel of the specified color in the RGB colorspace. Returns an integer from 0 to 255.

For more information about the RGB colorspace, see: https://en.wikipedia.org/wiki/RGB

Syntax

=GREEN(color)

Arguments
Argument Type Description
color Color The color
Examples

=GREEN("#0000ff") 0 Gets the green channel for the color represented by the hex color code #0000ff (blue)

=GREEN(RGB(128, 128, 128)) 128 Gets the green channel for a color having a value of 128 for red, 128 for green, and 128 for blue

=GREEN(0) #ERROR! Attempts to get the green channel from an integer, which is invalid because it cannot be converted to a color

HSVHUE

Gets the hue channel of the specified color in the HSV colorspace. Returns an integer from 0 to 360.

For more information about hue, see: https://en.wikipedia.org/wiki/Hue

Syntax

=HSVHUE(color)

Arguments
Argument Type Description
color Color The color
Examples

=HSVHUE("#0000ff") 240 Gets the hue for the color represented by the hex color code #0000ff (blue)

=HSVHUE(RGB(0, 255, 0)) 120 Gets the hue for a color having an RGB value of green

=HSVHUE(0) #ERROR! Attempts to get the hue from an integer, which is invalid because it cannot be converted to a color

HSVSATURATION

Gets the saturation channel of the specified color in the HSV colorspace. Returns a decimal number from 0 to 1.

For more information about hue, see: https://en.wikipedia.org/wiki/HSL_and_HSV#Saturation

Syntax

=HSVSATURATION(color)

Arguments
Argument Type Description
color Color The color
Examples

=HSVSATURATION("#bf4040") 0.66492146559685864 Gets the saturation for the color represented by the hex color code #bf4040 (dark red)

=HSVSATURATION(RGB(0, 255, 0)) 1 Gets the saturation for a color having an RGB value of green

=HSVSATURATION(0) #ERROR! Attempts to get the saturation from an integer, which is invalid because it cannot be converted to a color

HSVVALUE

Gets the value channel of the specified color in the HSV colorspace. Returns a decimal from 0 to 1.

For more information about value, see: https://en.wikipedia.org/wiki/HSL_and_HSV#Hue_and_chroma

Syntax

=HSVVALUE(color)

Arguments
Argument Type Description
color Color The color
Examples

=HSVVALUE("#0000ff") 1 Gets the value for the color represented by the hex color code #bf4040 (dark red)

=HSVVALUE(RGB(0, 128, 0)) 0.5019607843137255 Gets the value for a color having an RGB value of green

=HSVVALUE(0) #ERROR! Attempts to get the HSV value from an integer, which is invalid because it cannot be converted to a color

HUE

Gets the hue channel of the specified color in the HSL colorspace. Returns an integer from 0 to 360.

For more information about hue, see: https://en.wikipedia.org/wiki/Hue

Syntax

=HUE(color)

Arguments
Argument Type Description
color Color The color
Examples

=HUE("#0000ff") 240 Gets the hue for the color represented by the hex color code #0000ff (blue)

=HUE(RGB(0, 255, 0)) 120 Gets the hue for a color having an RGB value of green

=HUE(0) #ERROR! Attempts to get the hue from an integer, which is invalid because it cannot be converted to a color

LIGHTNESS

Gets the lightness channel of the specified color in the HSL colorspace. Returns a decimal number from 0 to 1.

For more information about hue, see: https://en.wikipedia.org/wiki/HSL_and_HSV#Lightness

Syntax

=LIGHTNESS(color)

Arguments
Argument Type Description
color Color The color
Examples

=LIGHTNESS("#bf4040") 0.5 Gets the lightness for the color represented by the hex color code #bf4040 (dark red)

=LIGHTNESS(RGB(255, 255, 255)) 1 Gets the lightness for a color having an RGB value of white

=LIGHTNESS(0) #ERROR! Attempts to get the lightness from an integer, which is invalid because it cannot be converted to a color

LUMINANCE

Calculates the luma (perceptual brightness) of the specified color. Uses SMPTE C / Rec. 709 coefficients, as recommended in WCAG 2.0.

For more information, see: https://www.w3.org/TR/2008/REC-WCAG20-20081211/#relativeluminancedef

Syntax

=LUMINANCE(color)

Arguments
Argument Type Description
color Color The color
Examples

=LUMINANCE("#0000ff") 0.0722 Gets the luminance for the color represented by the hex color code #0000ff (blue)

=LUMINANCE(RGB(0, 255, 0)) 0.7152 Gets the luminance for a color having a value of 128 for red, 128 for green, 128 for blue, and 0.5 for alpha

=LUMINANCE(0) #ERROR! Attempts to get the luminance from an integer, which is invalid because it cannot be converted to a color

RED

Gets the red channel of the specified color in the RGB colorspace. Returns an integer from 0 to 255.

For more information about the RGB colorspace, see: https://en.wikipedia.org/wiki/RGB

Syntax

=RED(color)

Arguments
Argument Type Description
color Color The color
Examples

=RED("#0000ff") 0 Gets the red channel for the color represented by the hex color code #0000ff (blue)

=RED(RGB(128, 0, 0)) 128 Gets the red channel for a color having a value of 128 for red, 0 for green, and 0 for blue (dark red)

=RED(0) #ERROR! Attempts to get the red channel from an integer, which is invalid because it cannot be converted to a color

SATURATION

Gets the saturation channel of the specified color in the HSL colorspace. Returns a decimal number from 0 to 1.

For more information about hue, see: https://en.wikipedia.org/wiki/HSL_and_HSV#Saturation

Syntax

=SATURATION(color)

Arguments
Argument Type Description
color Color The color
Examples

=SATURATION("#bf4040") 0.4980392156862745 Gets the saturation for the color represented by the hex color code #bf4040 (dark red)

=SATURATION(RGB(0, 255, 0)) 1 Gets the saturation for a color having an RGB value of green

=SATURATION(0) #ERROR! Attempts to get the saturation from an integer, which is invalid because it cannot be converted to a color

Color Operations

CONTRAST

Determines which of the two specified colors has a higher contrast against the given color.

Syntax

=CONTRAST(color, dark, light, threshold)

=CONTRAST(color)

Arguments
Argument Type Description
color Color The color to use when determining contrast.
dark Color (Optional) The dark color to use. Default to black.
light Color (Optional) The light color to use. Default to white.
threshold Number (Optional) The percentage threshold specifying where the transition from dark to light is. Defaults to 0.43
Examples

=CONTRAST("#00ff00") #000000ff Determines whether #00ff00 (green) has higher contrast with black or white.

=CONTRAST("#ff0000", "#00ff00", "#0000ff", 0.2) #0000ffff Determines whether #ff0000 (red) has higher contrast with #00ff00 (green) or #0000ff (blue).

DARKEN

Creates a new color from the specified color by decreasing the lightness in the HSL color space.

Syntax

=DARKEN(color, amount)

Arguments
Argument Type Description
color Color The color to darken.
amount Number The percentage amount to darken the given color, from 0 to 1.
Examples

=DARKEN("#ffffff", 0.5) #808080ff Darkens the color white by 50%.

=DARKEN(RGB(0, 255, 0), 0.2) #009900ff Darkens the color green by 20%.

DESATURATE

Creates a new color from the specified color by decreasing the saturation in the HSL color space.

Syntax

=DESATURATE(color, amount)

Arguments
Argument Type Description
color Color The color to desaturate.
amount Number The percentage amount to desaturate the given color, from 0 to 1.
Examples

=DESATURATE("#bf4040", 0.3) #996666ff Desaturate the color #bf4040ff by 20%.

FADE

Creates a new color from the specified color by setting the opacity to a specific value.

Syntax

=FADE(color, amount)

Arguments
Argument Type Description
color Color The color to fade.
amount Number The opacity value, from 0 to 1.
Examples

=FADE("#0000ffff", 0.5) #0000ff7f Sets the opacity of the color #0000ffff (blue, 100% opacity) to 50%.

FADEIN

Creates a new color from the specified color by increasing the opacity.

Syntax

=FADEIN(color, amount)

Arguments
Argument Type Description
color Color The color to fade in.
amount Number The percentage amount to increase opacity the given color, from 0 to 1.
Examples

=FADEIN("#00ff0088", 0.4) #00ff00ee Increase the opacity of the color #00ff0088 by 40%.

FADEOUT

Creates a new color from the specified color by decreasing the opacity.

Syntax

=FADEOUT(color, amount)

Arguments
Argument Type Description
color Color The color to fade out.
amount Number The percentage amount to decrease opacity the given color, from 0 to 1.
Examples

=FADEOUT("#00ff0088", 0.4) #00ff0021 Decrease the opacity of the color #00ff0088 by 40%.

GRAYSCALE / GREYSCALE

Creates a new color from the specified color by removing all saturation in the HSL color space.

Syntax

=GRAYSCALE(color)

=GREYSCALE(color)

Arguments
Argument Type Description
color Color The color to convert to grayscale.
Examples

=GRAYSCALE("#00ff00") #808080ff Converts the color #00ff00ff to grayscale.

=GRAYSCALE("#1071e5") #7b7b7bff Converts the color #1071e5ff to grayscale.

LIGHTEN

Creates a new color from the specified color by increasing the lightness in the HSL color space.

Syntax

=LIGHTEN(color, amount)

Arguments
Argument Type Description
color Color The color to lighten.
amount Number The percentage amount to lighten the given color, from 0 to 1.
Examples

=LIGHTEN("#000000", 0.5) #808080ff Increases the lightness of the color #000000ff by 50%.

=LIGHTEN(RGB(0, 255, 0), 0.5) #66ff66ff Increases the lightness of the color #00ff00ff (green) by 50%.

MIX

Creates a new color by combining two specified colors in a given proportion.

Syntax

=MIX(color1, color2, amount)

Arguments
Argument Type Description
color1 Color The first color to mix.
color2 Color The second color to mix.
amount Number The percentage amount to lighten the given color, from 0 to 1.
Examples

=MIX("#00ff00", "#0000ff", 0.5) #008080ff Combines the colors #00ff00 and #0000ff in equal parts.

=MIX("#00ff00", "#0000ff", 0.1) #001ae6ff Creates a color by blending 10% of the color #00ff00 and 90% of the color #0000ff.

SATURATE

Creates a new color from the specified color by increasing the saturation in the HSL color space.

Syntax

=SATURATE(color, amount)

Arguments
Argument Type Description
color Color The color to change saturation.
amount Number The percentage amount to saturate the given color, from 0 to 1.
Examples

=SATURATE("#bf4040", 0.3) #e51a1aff Increases the saturation of the color #bf4040ff by 30%.

SHADE

Creates a new color by mixing the specified color with black in a given proportion.

Syntax

=SHADE(color, amount)

Arguments
Argument Type Description
color Color The color to shade.
amount Number The percentage balance point between the given color and black, from 0 to 1.
Examples

=SHADE("#00ff00", 0.5) #008000ff Mixes the color #00ff00ff with black in equal parts.

SPIN

Creates a new color from the specified color by rotating the hue in the HSL color space.

Syntax

=SPIN(color, amount)

Arguments
Argument Type Description
color Color The color to spin.
amount Number The number of degrees to rotate the hue for the given color, from 0 to 360.
Examples

=SPIN("#00ff00", 180) #ff00ffff Rotates the hue of the color #00ff00ff by 180 degrees.

TINT

Creates a new color by mixing the specified color with white in a given proportion.

Syntax

=TINT(color, amount)

Arguments
Argument Type Description
color Color The color to tint.
amount Number The percentage balance point between the given color and white, from 0 to 1.
Examples

=TINT("#00ff00", 0.5) #80ff80ff Mixes the color #00ff00ff with white in equal parts.

Color Blending

BLENDMULTIPLY

Creates a new color by multiplying the corresponding RGB channels of the specified colors, resulting in a darker color.

Syntax

=BLENDMULTIPLY(color1, color2)

Arguments
Argument Type Description
color1 Color The first color to blend
color2 Color The second color to blend
Examples

=BLENDMULTIPLY("#0080ff", "#101010") #000810ff

=BLENDMULTIPLY("#ffffff", "#2080d0") #2080d0ff

BLENDSCREEN

Creates a new color by inverting and then multiplying the corresponding RGB channels of the specified colors, resulting in a lighter color.

Syntax

=BLENDSCREEN(color1, color2)

Arguments
Argument Type Description
color1 Color The first color to blend
color2 Color The second color to blend
Examples

=BLENDSCREEN("#0080ff", "#101010") #1088ffff

=BLENDSCREEN("#2080d0", "#ffffff") #ffffffff

BLENDOVERLAY

Creates a new color from the two provided by performing an overlay blend, making light colors lighter and dark colors darker.

Syntax

=BLENDOVERLAY(color1, color2)

Arguments
Argument Type Description
color1 Color The first color to blend
color2 Color The second color to blend
Examples

=BLENDOVERLAY("#2080d0", "#2080d0") #0880eeff

=BLENDOVERLAY("#0080ff", "#ffffff") #00ffffff

BLENDSOFTLIGHT

Creates a new color by performing a soft light blend.

Syntax

=BLENDSOFTLIGHT(color1, color2)

Arguments
Argument Type Description
color1 Color The first color to blend
color2 Color The second color to blend
Examples

=BLENDSOFTLIGHT("#2080d0", "#2080d0") #0b80deff

=BLENDSOFTLIGHT("#0080ff", "#ffffff") #00b5ffff

BLENDHARDLIGHT

Creates a new color by performing a hard light blend.

Syntax

=BLENDHARDLIGHT(color1, color2)

Arguments
Argument Type Description
color1 Color The first color to blend
color2 Color The second color to blend
Examples

=BLENDHARDLIGHT("#2080d0", "#2080d0") #0880eeff

=BLENDHARDLIGHT("#0080ff", "#ffffff") #ffffffff

BLENDDIFFERENCE

Creates a new color by subtracting one provided color from the other.

Syntax

=BLENDDIFFERENCE(color1, color2)

Arguments
Argument Type Description
color1 Color The first color to blend
color2 Color The second color to blend
Examples

=BLENDDIFFERENCE("#00ffff", "#0080ff") #007f00ff

BLENDEXCLUSION

Creates a new color by performing an exclusion blend.

Syntax

=BLENDEXCLUSION(color1, color2)

Arguments
Argument Type Description
color1 Color The first color to blend
color2 Color The second color to blend
Examples

=BLENDEXCLUSION("#0080ff", "#ffffff") #ff7f00ff

=BLENDEXCLUSION("#0080ff", "#000000") #0080ffff

BLENDAVERAGE

Blends the two provided colors by averaging the respective values in each channel.

Syntax

=BLENDAVERAGE(color1, color2)

Arguments
Argument Type Description
color1 Color The first color to blend
color2 Color The second color to blend
Examples

=BLENDAVERAGE("#00ff00", "#0000ff") #008080ff

BLENDNEGATION

Creates a new color from the two provided using a negation function.

Syntax

=BLENDNEGATION(color1, color2)

Arguments
Argument Type Description
color1 Color The first color to blend
color2 Color The second color to blend
Examples

=BLENDNEGATION("#1180f0", "#1180f0") #22fe1eff

=BLENDNEGATION("#00ffff", "#0080ff") #007f00ff