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 has a property named "Value" with 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, imagine 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:

MAP is called on [1, 2, 3]
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 item.

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

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

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

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

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"

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)

SIBLINGS

Gets info about all the sibling elements of the current item. Siblings are all elements that are children of the item's parent, excluding the item itself.

If you are in page data, then "siblings" is an empty array.

Syntax

=SIBLINGS

Examples

In the following examples, the selected shape is Shape 2, which is contained in 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

=SIBLINGS ["Shape 1", "Shape 3", "Shape 4"]

The array of all children of the current shape.

=SIBLINGS."Property 1" [1, 2, 6] The array of "Property 1" values of all siblings of the current shape.

=SIBLINGS."Property 2" [10, 14] The array of "Property 2" values of all siblings 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 for that shape in the resulting array.

=SIBLINGS."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.

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. for example =@"Property 1"

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

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

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

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 / COLLECTION

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.

Shape Properties

ITEMTYPE

Gets the type of the current item or the specified item(s). Item type is one of: Line, Block, Group, Page.

Syntax

=ITEMTYPE

=ITEMTYPE(items)

Arguments

Argument	Type	Description
items	Reference or Array	The item or items to return item types for

Examples

=ITEMTYPE Line

=ITEMTYPE(CHILDREN) [Block, Line]

SHAPEACTIONS

Gets all actions added to the current shape or specified shape(s). Shape actions are objects with an action type value and additional values specific to the type of action.

Syntax

=SHAPEACTIONS

=SHAPEACTIONS(shapes)

Arguments

Argument	Type	Description
shapes	Reference or Array	The shape or shapes for which to retrieve shape actions

Output

Shape actions can be one of the following types:

url
toggle-layer
hide-layer
show-layer
page
document
conditionalformatting

URL Shape Actions

URL shape actions have the url type, as well as the following fields:

Field	Type	Description
url	String	URL specified in the web/email action

Page Shape Actions

Page shape actions have the page type, as well as the following fields:

Field	Type	Description
page	Reference	The page specified in the page action

Document Shape Actions

Document shape actions have the document type, as well as the following fields:

Field	Type	Description
document	String	The document ID specified in the document action

Toggle Layer, Show Layer, and Hide Layer Shape Actions

Layer shape actions have a type of toggle-layer, show-layer, or hide-layer (for Toggle Layer, Show Layer, and Hide Layer actions, respectively), as well as the following fields:

Field	Type	Description
layers	Array	An array of references to layers specified in the action

Conditional Formatting Shape Actions

Conditional formatting shape actions have the conditionalformatting type, as well as the following fields:

Field	Type	Description
rules	Array	An array of conditional formatting rule names specified in the action

Examples

=SHAPEACTIONS [{"type":url, "url":https://lucid.co}]

=SHAPEACTIONS [{"type":toggle-layer, "layers":[Layer1,Layer2]}]

=SHAPEACTIONS [{"type":hide-layer, "layers":[Layer3,Layer4]}]

=SHAPEACTIONS [{"type":show-layer, "layers":[Layer5,Layer6]}]

=SHAPEACTIONS [{"type":page, "page":Page 1}]

=SHAPEACTIONS [{"type":document, "document":"00000000-0000-0000-0000-000000000000"}]

=SHAPEACTIONS [{"type":conditionalformatting, "rules":[Rule 1,Rule 2]}]

SHAPETYPE

Gets the specific type of shape for the current shape or the specified shape(s).

Syntax

=SHAPETYPE

=SHAPETYPE(shapes)

Arguments

Argument	Type	Description
shapes	Reference or Array	The shape or shapes to return shape types for

Examples

=SHAPETYPE DefaultSquareBlock

=SHAPETYPE(CHILDREN) [DefaultSquareBlock, DefaultStickyNoteBlock]

=LASTMODIFIEDTIME 2022-10-21 09:05:06.007

Page Properties

PAGENAME

Gets the name of the current page.

Syntax

=PAGENAME

Examples

=PAGENAME Page 2

=PAGENAME(PAGE) Page 2

PAGENUMBER

Gets the page number of the current page.

Syntax

=PAGENUMBER

Examples

=PAGENUMBER 2

=PAGENUMBER(PAGE) 2

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

COALESCE

Returns the first non-empty value, or NONE if all values are empty. A value is non-empty if ISNOTEMPTY(<expr>) returns true. If there are no values passed in, it returns NONE.

Syntax

=COALESCE()

=COALESCE(expr1)

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

Arguments

Argument	Type	Description
exprN	String, Array or Object	Any value. The first non-empty value will be returned

Examples

=COALESCE() NONE With no arguments, COALESCE returns NONE.

=COALESCE("A") "A" The only argument is non-empty, so it gets returned.

=COALESCE("", "B", NONE) "B" Returns the first non-empty string.

=COALESCE(OBJECT(), NONE, []) NONE Every argument is empty, so COALESCE returns NONE.

=COALESCE(" ", OBJECT("A", 1)) OBJECT("A", 1) An object with keys is not empty, but a string of whitespace is.

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

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

ASSTRING

For simple arguments, this function converts the argument into a string representation. For references to Lucid shapes, extracts the string contents of the shape and returns it.

Arguments

Argument	Type	Description
input	Any	The value to convert to a string

Examples

=ASSTRING(1) "1"

=ASSTRING([1,2]) "[1,2]"

=ASSTRING(OBJECT('A', 1)) "{"A": 1}"

=ASSTRING(this) "Shape Text"

CHAR / UNICHAR

Get a string from the given character code or codes.

Syntax

=CHAR(input1, input2, ..., inputN)

Arguments

Argument	Type	Description
input	Number	Character code

Examples

=CHAR(74) "J"

=CHAR(74, 75) "JK"

=CHAR(127482, 127480) "🇺🇸"

CODE / UNICODE

Convert a unicode string into their corresponding code point or points.

Syntax

=CODE(string)

Arguments

Argument	Type	Description
string	String	Characters to decode

Examples

=CODE("J") 74

=CODE("JK") "[74, 75]"

=CODE("🇺🇸") "[127482, 127480]"

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

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"

REPT

Repeats a string a number of times.

Syntax

=REPT(string, repetitions)

Arguments

Argument	Type	Description
string	String	The specified string
repetitions	Number	The number of times to repeat the string

Examples

=REPT("Hello!", 3) "Hello!Hello!Hello!"

=REPT("na ", 8) "na na na na na na na na"

=REPT("Nothing", 0) ""

RIGHT

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

SEARCH

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

ASNUMBER

Converts a value into a number.

Syntax

=ASNUMBER(value)

Arguments

Argument	Type	Description
value	String or Currency	The value to convert to a number

Examples

=ASNUMBER("1") 1

=ASNUMBER("123.45") 123.45

=ASNUMBER(CURRENCY(1)) 1

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 e^value.

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

FORMAT_NUMBER

Returns a string representing the number, formatted according to the given pattern.

Syntax

=FORMAT_NUMBER(value, pattern)

Arguments

Argument	Type	Description
value	Number	The value to format
pattern	String	The pattern to format by

Examples

=FORMAT_NUMBER(123.456, ".?") "123.5"

=FORMAT_NUMBER(123.456, "$ 0") $123

=FORMAT_NUMBER(123.456, "0.00e+00") 1.23e+2

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

NUMBER_PARSE

Liberally parses the first number in an input string.

Syntax

=NUMBER_PARSE(input)

Arguments

Argument	Type	Description
input	String	The string to parse a number from

Examples

=NUMBER_PARSE("123") 123

=NUMBER_PARSE("Hello world! -123 456") -123

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 / SQUAREROOT

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

ASARRAY

If the argument is not an array, returns an array containing the argument. Otherwise, returns the argument array.

Syntax

=ASARRAY(value)

Arguments

Argument	Type	Description
value	Any	The value to be returned as an array

Examples

=ASARRAY(1) [1]

=ASARRAY("ABC") ["ABC"]

=ASARRAY([1, 2, 3]) [1, 2, 3]

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

DIFFERENCE

Takes two arrays, treated as sets (i.e. ignoring duplicate values), and returns a new array that contains all items from the first array that are not in the second array.

Syntax

=DIFFERENCE(array1, array2)

Examples

=DIFFERENCE([1, 2, 3], [3, 4, 5]) [1, 2] =DIFFERENCE(["A", "B"], [1, 2]) ["A", "B"] =DIFFERENCE(["A", "B", "B"], [1, 2]) ["A", "B"] =DIFFERENCE([OBJECT("A", 1), OBJECT("B", 1)], [OBJECT("A", 1)]) [{"B": 1}]

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

REDUCE

Returns a value resulting from applying a formula to each element of the array and the previous result.

Syntax

=REDUCE(initialValue, array, expression)

Arguments

Argument	Type	Description
initialValue	Any	Initial value for the previous result
array	Array	Array of elements
expression	Lambda	Transformation to perform on each item of the array

Examples

=REDUCE(0, [10, 20, 30], x => x + x.previous) 60 REDUCE function is called for each item in the array (the values 10, 20, and 30). For each item, the value is added to the previous result. The last result is returned for the function, resulting in the value 60.

REVERSE

Reverses the order of an array.

Syntax

=REVERSE(array)

Arguments

Argument	Type	Description
array	Array	The array of values to reverse

Examples

=REVERSE([1, 2, 3, 4]) [4, 3, 2, 1]

SCAN

Returns an array constructed from each of the results of applying a formula to each element of the array and the previous result.

Syntax

=SCAN(initialValue, array, expression)

Arguments

Argument	Type	Description
initialValue	Any	Initial value for the previous result
array	Array	Array of elements
expression	Lambda	Transformation to perform on each item of the array

Examples

=SCAN(1, [1, 2, 3], x => x * x.previous) [1, 2, 6] SCAN formula is called for each item in the array (the values 1, 2, and 3). For each item, the value is multiplied by the previous value, resulting in an array containing the running multiplication: [1, 2, 6].

SHUFFLE

Randomly shuffles the values in the given array.

Syntax

=SHUFFLE(array)

Arguments

Argument	Type	Description
array	Array	The array of values to shuffle

Examples

=SHUFFLE([1, 2, 3, 4, 5]) [4, 5, 2, 1, 3]

SKIP

Returns an array consisting of remaining items from an array after skipping the specified number of items at the beginning.

Syntax

=SKIP(array, count)

Arguments

Argument	Type	Description
array	Array	The array of values
count	Number	The number of items at the beginning of the array to skip

Examples

=SKIP([1, 2, 3, 4, 5], 3) [4, 5]

=SKIP(["A", "B", "C"], 1) ["B", "C"]

=SKIP([], 4) []

=SKIP(["x", "y", "z"], 4) []

SKIPWHILE

Returns an array consisting of remaining items from an array after skipping all items at the beginning which satisfy the specified condition.

Syntax

=SKIPWHILE(array, test)

Arguments

Argument	Type	Description
array	Array	The array of values
test	Lambda	Test to perform on each item of the array to determine if the item is skipped

Examples

=SKIPWHILE([1, 1, 1, 3, 5, 1], x => x = 1) [3, 5, 1]

=SKIPWHILE(["A", "B", "C", "d", "E", "f"], x => UPPER(x) = x) ["d", "E", "f"]

TAKE

Returns an array consisting of the specified number of items from the beginning of an array.

Syntax

=TAKE(array, count)

Arguments

Argument	Type	Description
array	Array	The array of values
count	Number	The number of items at the beginning of the array to include

Examples

=TAKE([1, 2, 3, 4, 5], 3) [1, 2, 3]

=TAKE(["A", "B", "C"], 1) ["A"]

=TAKE([], 4) []

=TAKE(["x", "y", "z"], 4) ["x", "y", "z"]

TAKEWHILE

Returns an array consisting of all items from the beginning of an array which satisfy the specified condition.

Syntax

=TAKEWHILE(array, test)

Arguments

Argument	Type	Description
array	Array	The array of values
test	Lambda	Test to perform on each item of the array to determine if the item is included

Examples

=TAKEWHILE([1, 3, 5, 2, 4, 6, 1], x => ISODD(x)) [1, 3, 5]

=TAKEWHILE(["A", "B", "C", "d", "E", "f"], x => UPPER(x) = x) ["A", "B", "C"]

UNION

Takes two arrays, treated as sets (i.e. ignoring duplicate values), and returns a new array that contains all distinct items from both inputs.

Syntax

=UNION(array1, array2)

Examples

=UNION([1, 2, 3], [3, 4, 5]) [1, 2, 3, 4, 5] =UNION(["A", "B"], [1, 2]) ["A", "B", 1, 2] =UNION([OBJECT("A", 1), OBJECT("B", 1)], [OBJECT("A", 1), OBJECT("C", 2)]) [{"A": 1}, {"B": 1}, {"C": 2}]

UNIQUE

Takes either a single array or a list of values and returns an array which contains each distinct value only once. In other words it turns either an array or a list of values into a set.

Syntax

=UNIQUE(array)

=UNIQUE(val1, val2, ...)

Examples

=UNIQUE([1, 2, 3, 2, 1]) [1, 2, 3] =UNIQUE(1, 2, 3, 2, 1) [1, 2, 3] =UNIQUE([1, 2, 3, "A", "B", "B", 2, "C"]) [1, 2, 3, "A", "B", "C"] =UNIQUE([2, "A", 1, 3, "C", "B", 2, "A"]) [2, "A", 1, 3, "C", "B"] =UNIQUE([1, 2, 3], [1, 2], [1, 2]) [[1, 2, 3], [1, 2]] =UNIQUE([OBJECT("A", 1), OBJECT("A", 2), OBJECT("A", 1)]) [{"A": 1}, {"A": 2}]

ZIP

Combines the items in several input arrays by putting the first item from each array into the first output array, the second item from each array into the second output array, and so on. Specifically, given n arrays of at least m values each, returns an array of m arrays of n values each, where the ith value in the jth output array is the jth value in the ith input array.

Syntax

=ZIP(array1, array2, ...., arrayN)

Arguments

Argument	Type	Description
array	Array	Array of items to combine

Examples

=ZIP([1, 2, 3], ["a", "b", "c"]) [[1, "a"], [2, "b"], [3, "c"]]

=ZIP([1, 2, 3, 4], ["a", "b"]) [[1, "a"], [2, "b"]]

=ZIP([1, 2, 3, 4], ["a", "b"], ["Z", "Y", "Z"]) [[1, "a", "Z"], [2, "b", "Y"]]

ZIPALL

Combines the items in several input arrays by putting the first item from each array into the first output array, the second item from each array into the second output array, and so on, using the default value for any missing items. Specifically, given n arrays of as many as m values each, returns an array of m arrays of n values each, where the ith value in the jth output array is the jth value in the ith input array or a default value.

Syntax

=ZIPALL(array1, default1, array2, default2, ...., arrayN, defaultN)

Arguments

Argument	Type	Description
array	Array	Array of items to combine
default	Any	Default value to use for any missing items

Examples

=ZIPALL([1, 2, 3], ["a", "b", "c"]) [[1, "a"], [2, "b"], [3, "c"]]

=ZIPALL([1, 2, 3, 4], 0, ["a", "b"], "z") [[1, "a"], [2, "b"], [3, "z"], [4, "z"]]

Dates

CURRENTSECOND

The current time's seconds field.

Syntax

=CURRENTSECOND

Examples

=CURRENTSECOND 43

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

Currency

CURRENCY

Creates a currency from the specified amount and type, defaulting to USD if the type isn't specified.

Syntax

=CURRENCY(value, type)

Arguments

Argument	Type	Description
value	Number	Amount of currency
type	String	(Optional) The type of currency, defaults to USD. List of options found here

Examples

=CURRENCY(9.99) $9.99

=CURRENCY(1, "EUR") €1.00

=CURRENCY(55, "AUD") A$55.00

CURRENCYADD

Creates a new currency by adding two currencies of the same type together.

Syntax

=CURRENCYADD(currency, currency)

Arguments

Argument	Type	Description
currency	Currency	Amount

Examples

=CURRENCYADD(CURRENCY(0.1), CURRENCY(0.2)) $0.30

=CURRENCYADD(CURRENCY(0.1, "EUR"), CURRENCY(0.2, "EUR")) €0.30

=CURRENCYADD(CURRENCY(0.1, "AUD"), CURRENCY(0.2, "AUD")) A$0.30

CURRENCYDIFF

Calculates the difference between two specified currencies of the same type.

Syntax

=CURRENCYDIFF(currency, currency)

Arguments

Argument	Type	Description
currency	Currency	Amount

Examples

=CURRENCYDIFF(CURRENCY(0.2), CURRENCY(0.3)) $0.10

=CURRENCYDIFF(CURRENCY(0.1, "EUR"), CURRENCY(0.2, "EUR")) €0.10

=CURRENCYDIFF(CURRENCY(0.1, "AUD"), CURRENCY(0.2, "AUD")) A$0.10

=CURRENCYDIFF(CURRENCY(0.1, "AUD"), CURRENCY(0.2, "EUR")) #ERROR! Attempts to get the difference between two currencies, but it's invalid because they are of different types

CURRENCYMULT

Creates a new currency by multiplying a currency by a number.

Syntax

=CURRENCYMULT(currency, value)

Arguments

Argument	Type	Description
currency	Currency	Amount
value	Number	Value to multiply currency by

Examples

=CURRENCYMULT(CURRENCY(0.1), 3) $0.30

=CURRENCYMULT(CURRENCY(0.2, "EUR"), 3) €0.60

=CURRENCYMULT(CURRENCY(0.3, "AUD"), 3.5) A$1.05

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

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.

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