How to Use Formulas

Read on to learn how to use formulas on Lucidchart.

Lucidchart rounds out its shape data capabilities by offering formulas to users. Much the way spreadsheet tools allow users to analyze, adjust, and quantify their data, Lucidchart 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.

formulas_getting_started_one.png You can also put formulas into the custom metadata properties - but why? Well, consider this example: we have a BPMN process diagram, and each step has a custom metadata property called 'Estimated Time (hr)'.

formulas_getting_started_one.png

We would like to know what the total estimated time for the process is, and we can use formulas to do it. In this example, we are going to use our formulas on the page. So, we click on the basic canvas and open up the data panel to view the page-level metadata.

formulas_getting_started_two.gif

On the page data, we add a property called "Total Time" and use a formula to get us all the values associated with the property name 'Estimated Time (hr)', from the shapes on the page. We can then use another formula, =SUM, to get the total estimated time.

This is just one example of how you might get started using formulas in Lucidchart - explore below to see the other types of formulas that you can use and how they work.

Glossary:

  • Collection: A single sheet (tab) in an imported spreadsheet data set.
  • Reference Key: The column in a collection that uniquely identifies a row. For example, each person has a unique SSN but may not have a unique name. The SSN 123-01-4567 uniquely refers to person "John Albert Smith", but the name "John Smith" could refer to thousands of people.
  • Custom Data: Shape data that is added manually, rather than referenced by a Reference Key.
  • Label and Value: Shape data comes in Label: Value pairs. For example, "Name: John Smith" has label "Name" and value "John Smith"
  • Element: Shape, line, page, group, etc. in Lucidchart
  • Keyed Values: Shape data that is reference by a Reference Key
  • Parent: The Lucidchart group to which an element belongs. Does not refer to elements upstream or downstream.
  • Child: An element of the selected group or page. Does not refer to elements upstream or downstream.
  • Descendant: The children and all of their children and all of their children, etc.


Formulas can call:

Shape data (any data attached to the current shape):

  • i.e. with custom data:{{=SUM(@customdata."Number A", @customdata."Number B")}}

    a.png
  • i.e. with keyed data: {{=SUM(@"MyData"."Number A", @"MyData"."Number B")}}

    b.png


Reference keys (any data associated with that reference key, regardless of link to shapes),

  • i.e. {{=SUM(LOOKUP("MyData", "Key 01")."NumberA",LOOKUP("MyData", "Key 02")."NumberB")}}


Collection data (data pulled directly from your data source) in your formulas.

  • i.e. {{=SUM("MyData"!E2, "MyData"!E3)}}


Formulas can be written:

  • On the shape (need {{-syntax)
  • In the shape data panel


Other Background Information

All formulas start with =

  • And {{= when typing directly in the shape


When the input for a formula is shape data attached to the selected shape, @ and this. mean the same

  • For example, =@"Name" is the same as =this."Name"
  • Reference key data takes preference over custom data. For example, if custom data and keyed data have the same label, THIS will return the value (or an array or values) of the keyed data


When in doubt, put double quotes (") around the collection name, the reference key value, and all field names

  • For example, write =LOOKUP("MyData", "A01")."Name" instead of =LOOKUP(MyData, A01).Name
Using formulas, dynamic shapes, and smart containers in Lucidchart, you can easily visualize your progress on projects or towards goals.

Let's say you are the content manager overseeing a collaborative project to improve the quality of articles in your Help Center. There are 30 articles, and your team has evaluated them on a scale of A to F to find that the average grade is a C-/D+. By making improvements to enough of the articles, your team aspires to bring the average grade to a A-/B+ by the end of the quarter.

To keep your team on the same page, you want to create a dashboard that displays the articles' distribution across the grading rubric and your team's progress towards your goal.

See the sections below to learn how to create this dashboard in Lucidchart using smart containers, dynamic shapes, and formulas!

Screen_Shot_2019-12-03_at_3.47.34_PM.png

Using Lucidchart's smart containers, you can easily visualize the distribution of Help Center articles across a grading scale. First, add the smart containers shape library to a new blank document, and set the "Group by" value to "Status."

Screen_Shot_2019-12-03_at_3.52.25_PM.png

Then create a container for each grade of your scale, eg. A through F, and fill each container with tiles to correspond with the articles that have been evaluated to that container's grade. Style the containers as you like, and you are all set!

Screen_Shot_2019-12-03_at_3.54.08_PM.png
The next step to create your dazzling dashboard is to assign numerical values to each of the grade containers so that you can quantitatively measure your team's progress (eg. A=10, B=8, C=6, D=4, F=2). You can do so by assigning custom data fields to each container in the data panel, which you can access by clicking the database icon in the dock.

To add a custom data field to one of your containers, you will need to double click on the specific container (rather than clicking once and selecting the entire smart containers grouped shape). Start with the A container, and note that the data panel changes to ContainerView.

data_panel_container_view.png

To add a custom data field, click the + sign next in the "Custom Data" header.

data_panel___custom_data.png

Rename the field to "Points," and enter 10 as the field's value. Follow the same steps with the other 4 containers in your dashboard.
Now that you have associated a numerical value with each container of your dashboard, it is time to create a formula that multiplies the amount of tiles (articles) with the point allocation for their container.

In order to accomplish this, you will first need a formula that calculates the total points per container. Double-click into the A container again, and add another custom data field. Title this one Total Points. Enter the following formula into the value bar:

=PRODUCT(count(CONTENTS),THIS."Points")

This formula should return the points allocation for the container (10) multiplied by the number of tiles in that container. If there are two tiles in the container, it will return 20.

Screen_Shot_2019-12-03_at_3.59.22_PM.png

You can copy and paste the same formula in a Total Points field for all of the containers in your dashboard, and it should return a unique value each time.
After calculating the total points associated with each container, you will want to write a formula that adds these values together to determine the total amount of points that your articles currently add up to.

Since this value represents the collection of all of the containers in your dashboard, you should add it as a field to the collection of smart containers rather than a specific container. Click out of any of the containers that are currently selected, then click once anywhere on the smart container grouped shape to enter the data panel for the entire group.

Add a custom field to the data panel and title it "Points Grand Total." Enter the following formula into the value bar:

=SUM(children."Total Points").

This formula will return the sum of the "Total Points" fields for each of the grouped shape's "children" - that is, each container within the grouped collection.

Screen_Shot_2019-12-03_at_4.00.45_PM.png
Congrats - you are almost done creating your dashboard! There is just one step to go: creating a dynamic progress bar that will display your team's progress towards its goal.

Once you have added Lucidchart's dynamic shape library to your document, drag out one of the progress bars. Click once on this shape to open its view in the data panel.

You will want to set a minimum value for the progress bar that reflects the current state of your articles' collective quality review scores. According to the numerical values associated with your grades, this value would be 150 (30 articles x 5 points each, the average of C and D).

You will then want to set the maximum value to be the collective score that you hope to achieve by the end of the quarter. If you goal is to bring the average grade to A-/B+, then this value would be 270 (30 articles x 9 points, the average of B and A).

Finally, you will want the Value field of your progress bar to be a dynamic value that reflects the progress of your articles' qualities over the course of the quarter. You can use the following formula to achieve this:

=SUM(descendants(page)."Total Points")

Screen_Shot_2019-12-03_at_4.06.01_PM.png

This formula is similar to the one mentioned in the section above; the only difference is that you are calling the containers with the reference descendants(page)" rather than "children." This change is necessary because the progress bar shape, unlike the grouped container shape, is not a "parent" of the containers. Rather, you are referring to the containers as the "descendants" of the entire page of your document. Pretty cool, huh?
Voila! You have finished creating your dashboard. Now your team can get going on those article improvements!

When a team member makes enough improvements to an article that it moves up a grade, have them move the tile from the container representing the old grade to the new one. And watch the progress bar adjust accordingly, reflecting all your hard work!

Screen_Shot_2019-12-03_at_4.08.24_PM.png
THIS
    A reference to the shape itself.
  • =THIS → the text on the basic shape.
  • Mainly used to get things on this shape or to reference it.
  • ex: =THIS."Property 1" → gives us the value of "Property 1" on this shape
PAGE
  • Allows fast access to the page data values.
  • ex: =page."Property 1" → gives the page data value for "Property 1"
CHILDREN
  • Gets info about all the child elements of the current point.
  • If you are in page data, then "children" is all shapes (including group shapes and layers) on the page.
  • If you are inside a group shape or on a layer, then "children" is all shapes within the group or layer
  • NOTE: "children" only reflects the the immediate children of your current space. So, if a group shape is a "child" of your current space, then the shapes that are making up that group shape are not the "children" of the current space. They are "descendants", but NOT children. The group shape is the "child".
  • =CHILDREN
  • ex: =CHILDREN."Property 1" → [1, 4, 2, 6, 7, 8] (returns an array of the "Property 1" values of the children)
PARENT
  • Gets info about the parent element of the current point.
  • 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.
  • See above for diagram showing parents.
  • =PARENT
  • =PARENT."Property 1" → 5 (Gives the parent's value)
DESCENDANTS
  • Get info about all descendants of current space - children, and children of children, etc.
  • If you are on page data, every single shape on the canvas (even in groups shapes and layers) is a "descendant"
  • If you are in a group shape, every single shape within it (which may be group shapes that have more in them), broken down, is a descendant of the group shape space
  • =DESCENDANTS
  • ex: =DESCENDANTS."Property 1" → [1, 4, 2, 6, 7, 8, 7, 5, 4, 3, 2, 7, 8, 5, 6, 3, 2, 5] (returns an array of the "Property 1" values of the children and children's children and so on until there are no more children in that space)
CONNECTED
  • References all shapes that are connected directly to the current shape, via a line.
  • =CONNECTED
  • ex: =CONNECTED."Property 1" → 5 (if there is only one shape connected, then this will return the value from that one shape)
  • ex: =CONNECTED."Property 1" → [1, 4, 2, 6, 7] (returns an array of the "Property 1" values of the directly connected shapes)
UPSTREAM
  • References all shapes FROM which a line has been drawn TO the current shape (note: the arrows have no bearing on this)
  • If I have 2 shapes, A and B, and draw a line from A to B, then A is upstream of B. If I have another shape C, and I draw a line from B to C, then B is upstream of C. If I add another shape D, and draw a line from D to B, then D is upstream of B.
  • I would use UPSTREAM on B, to reference A and D
  • I would use UPSTREAM on C, to reference B
  • =UPSTREAM
  • ex: =UPSTREAM."Property 1" → 5 (if there is only one shape upstream, then this will return the value from that one shape)
  • ex: =UPSTREAM."Property 1" → [1, 4, 2] (returns an array of the "Property 1" values of the upstream shapes).
DOWNSTREAM
  • References all shapes TO which a line has been drawn FROM the current shape (note: the arrows have no bearing on this)
  • If I have 2 shapes, A and B, and draw a line from A to B, then B is downstream of A. If I have another shape C, and I draw a line from B to C, then C is downstream of B. If I add another shape D, and draw a line from D to B, then B is downstream of D.
  • I would use DOWNSTREAM on A, to reference B
  • I would use DOWNSTREAM on D, to reference B
  • =DOWNSTREAM
  • ex: =DOWNSTREAM."Property 1" → 5 (if there is only one shape downstream, then this will return the value from that one shape)
  • ex: =DOWNSTREAM."Property 1" → [1, 4, 2] (returns an array of the "Property 1" values of the downstream shapes).
CONNECTEDDEEP
  • References all shapes that are connected to the current shape, even through other shapes
  • If I have 2 shapes, A and B, and draw a line from A to B, then B is connected to A. If I have another shape C, and I draw a line from B to C, then C is connected to B, and deeply connected to A.
  • =CONNECTEDDEEP
  • ex: =CONNECTEDDEEP."Property 1" → [1, 4, 2, 4, 5, 7, 8] (returns an array of the "Property 1" values of the deeply connected shapes).
UPSTREAMDEEP
  • References all shapes FROM which a line has been drawn TO the current shape (note: the arrows have no bearing on this)
  • If I have 2 shapes, A and B, and draw a line from A to B, then A is upstream of B. If I have another shape C, and I draw a line from B to C, then B is upstream of C, and A is upstream deep of BOTH. If I have another shape D, and I draw a line from B to D, then B is upstream of D, and A is also upstream deep of BOTH.
  • I would use UPSTREAMDEEP on D, to reference B and A
  • I would use UPSTREAMDEEP on C, to reference B and A
  • =UPSTREAMDEEP
  • ex: =UPSTREAMDEEP."Property 1" → [1, 4, 2] (returns an array of the "Property 1" values of the all upstream shapes)
DOWNSTREAMDEEP
  • References all shapes TO which a line has been drawn FROM the current shape (note: the arrows have no bearing on this)
  • If I have 2 shapes, A and B, and draw a line from A to B, then B is downstream of A. If I have another shape C, and I draw a line from B to C, then C is downstream of B, and BOTH are downstream deep of A. If I have another shape D, and I draw a line from B to D, then D is downstream of B, and BOTH are downstream of A.
  • I would use DOWNSTREAMDEEP on B, to reference C and D
  • I would use DOWNSTREAMDEEP on A, to reference B, C, and D
  • =DOWNSTREAMDEEP
  • ex: =DOWNSTREAMDEEP."Property 1" → [1, 4, 2] (returns an array of the "Property 1" values of the all downstream shapes)
@
  • Shorthand to reference 'this current shape' when getting data properties.
  • =@'Property 1' returns whatever the value of the property called 'Property 1' on the current shape is.
  • ex: =@'Total' → returns 10, when the current shape has a property called 'Total' that equals 10.
LABEL
  • Gets the unique shape id for the shape it references
  • =LABEL(this), =LABEL(parent)
  • ex: =LABEL(this) → ab3756dhty!975 (returns the shape ID, unique to each shape)
  • Why? You can use this to then listen to specific shapes!
  • =#'shape_id'."Property Name" → will give you the value of "Property Name" from the shape whose ID you have passed!
  • ex: =#'shape_id'."Total" → 250. Then you can do something to this shape, based on the other shape's Total Value
[] or INDEX
  • Allows you to access the value at a given position in a list (must pass in the numbered position, starting at 1 - does not function as a 0-indexed list)
  • Works with deeply nested arrays!!
  • =list[index position] → returns the value at the given index of the list
  • =INDEX(list, index position) → returns the value at the given index of the given list
  • ex: =CHILDREN.'Employee Name'[3] → returns the 3rd employee name in the list
  • ex: =INDEX(CHILDREN.'Employee Name', 3) → returns the 3rd employee name in the list
  • ex: =INDEX(INDEX(DOWNSTREAMDEEP.'Connected', 2), 3) → returns the 2nd item from the list of connected items, of the 2nd item that is deeply downstream of the current shape.
    • At index 1 contains a list of everything connected to shape B, which are shapes [A, C]
    • At index 2 contains a list of everything connected to shape C, which are shapes [B, 1, 2, 3]
    • At index 3-5, contains a list of everything connected to shapes 1, 2, and 3 respectively, which in each case is just shape C.
    • So now, INDEX gets us the second item (index 2) of things deeply downstream of shape A - which is the list of "everything connected to shape C" → [B, 1, 2, 3]
      • Then, the outer INDEX gets us the third item (index 3) of the list of shapes connected to shape C [B, 1, 2, 3] → 2
      • So, we get 2.
    • In this case, you give the position of the list that you want within the current list, and then give the position of the item in the nested list.
    • Note that this nesting will work inside out, evaluating the most deeply nested terms first. So first, it gets us the CONNECTED shapes of everything deeply downstream of shape A.
    • A is upstream of B, B is upstream of C, and C is upstream of 1, 2, and 3. On shape A, DOWNSTREAMDEEP.CONNECTED = [ [A, C], [B, 1, 2, 3], C, C, C ]
    • This also works with the [] : =DOWNSTREAMDEEP.CONNECTED[2][3] → returns 2
FIELDLOOKUP
  • Uses a reference key and the collection name to find a data property.
  • =FIELDLOOKUP("Collection Name", "Shape Data Label", "Ref Key")
  • ex: =FIELDLOOKUP("Sheet1", "Employee Name", "123") → gives us the name of the employee who on Sheet1, has the ref key 123
LOOKUP
  • Finds data from a collection, by reference key, and returns an array of all the data associated with that reference key.
  • If you add a shape data property name (ex: column header), then it will give you that specific property.
  • =LOOKUP("Collection Name", "Reference Key") → will return ["All", "data", "from", "the", "row", "with", "the", "reference", "key"] as a list
  • =LOOKUP("Collection Name", "Reference Key")."Property Name" → will return "Value associated with that specific property"
  • ex: =LOOKUP("Sheet1", "123") → ["123", "Steve Rogers", "Captain America", "starspangledman@gmail.com"]
  • ex: =LOOKUP("Sheet1", "123")."Name" → "Steve Rogers"
&
  • Concatenates strings.
  • ='First String' & 'Second String' → returns 'First StringSecond String'
  • ex: ='Hello' & 'There' → returns 'HelloThere'
  • ex: ='Hello ' & 'There' → returns 'Hello There'
  • ex: ='Hello' & ' ' & 'There' → returns 'Hello There'
TRIM
  • Trims trailing whitespace from string passed in (text passed in) =TRIM(string)
  • =TRIM(" hi ") → "hi"
  • =TRIM("I love chocolate! ") → "I love chocolate!"
+, -, *, /
  • Typical Math operators behave as expected for addition, subtraction, multiplication, and division.
  • =1+2 → 3 etc.
  • Note: modulus (%) as seen in JS does not work!
PRODUCT
  • Gives the product of either an array or all numbers passed in with commas.
  • If complex array, like children.children, flattens the array to calculate.
  • =PRODUCT(children."Property 1")
  • =PRODUCT(1, 2, 3, 4, 5, 6, 7) → 1 * 2 * 3 * 4 * 5 * 6 * 7 → 5040
ASPERCENT
  • Takes a number and turns it into a Percent.
  • =ASPERCENT(num) → returns num as a %
  • ex: =ASPERCENT(.1) → 10%
  • ex: =ASPERCENT(.158) → 15.8%
  • ex: =ASPERCENT(1.4) → 140%
ROUND
  • Rounds a number to specified number of digits - default is 0.
  • =ROUND(number, number of digits to round to)
  • ex: =ROUND(5.6978695869) → 6
  • ex: =ROUND(5.6978695869, 1) → 5.7
  • ex: =ROUND(5.6978695869, 2) → 5.70
<, <=, =, >=, >, <>
  • Typical math & comparison operators work as expected for less than, less than or equal to, equal to, greater than or equal to, greater than, and not equal to.
  • ex: =5 < 10 → returns true
  • ex: =6 = 7 → returns false
  • ex: ='Hi' = 'Hi' → returns true (note: follows strict equivalency, so 'Hi' = 'hi' will be false!)
  • ex: =@'Property 1' > page.'Property 1' → will return true or false
  • ex: =5 <> 10 → returns true (5 is not equal to 10)
AND
  • Resolves to true if all of the given expressions/values are true, and false otherwise
  • =AND("expression", "expression2",....)
  • ex: =AND(this."Property 1" = 10, this."Property 2" > 5)
OR
  • Resolves to true if any of the given expressions/values are true, and false if none are true
  • =OR("expression", "expression2", "expression3", ....)
  • ex: =OR(this."Property 1" = 10, this."Property 2" > 5)
NOT
  • Resolves to true if the given expression/values is false, and false if the given expression is true
  • =NOT("expression")
  • ex: =NOT(10 < 1) → 10 < 1 is a false statement, so the function will read this as =NOT(false), which resolves to true
  • ex: =NOT(10 > 1) → 10 > 1 is a true statement, so the function will read this as =NOT(true), which resolves to false
IF
  • Resolves to one value if the statement give is true, and to the second value if it is false
  • =IF("expression", "value if true", "value if false")
  • ex: =IF(10 < 20, "Yes", "No") → resolves to "Yes" because 10 is less than 20.
  • ex: =IF(10 > 20, "Yes", "No") → resolves to "No" because 10 is not greater than 20.
  • Advanced ex:
    • this.A = 10
    • this.B = 15
    • this.C = 12
    • Nested If ex:
      • =IF(this.A > this.B, this.A, IF(this.B > this.C, this.B, this.C)) →
        • =IF(15 > 12, 15, 12) → 15 is greater than 12, so the answer resolves to 15
        • =IF(10 > 15, 10, IF(15 > 12, 15, 12)) → the first if, 10 > 15, is false, so now we focus on the second if statement
      ISEMPTY
      • Returns true if property value is empty
      • =ISEMPTY(this."Property 1")
      ISNOTEMPTY
      • Returns true if property value is not empty
      • =ISNOTEMPTY(this."Property 1")
String/Number Checks
ISODD
  • Checks if a number is odd. Resolves to true if odd, false if even.
  • =ISODD("number or value")
  • =ISODD(9) → true
  • =ISODD(10) → false
ISEVEN
  • Checks if a number is even. Resolves to true if even, false if odd.
  • =ISEVEN("number or value")
  • =ISEVEN(9) → false
  • =ISEVEN(10) → true
  • CONTAINS
    • Checks to see if a property or string contains a value (true if yes, false if no)
    • =CONTAINS("Value you are checking", "what you want to know if it contains")
    • ex: =CONTAINS(this."Property 1", "abc") → resolves to true if Property 1 contains "abc", and false otherwise
    DOESNOTCONTAIN
    • Checks to see if a property or string does NOT contains a value (true if it lacks the value, false if it does contain the value)
    • =DOESNOTCONTAIN("Value you are checking", "what you want to know if it contains")
    • ex: =DOESNOTCONTAIN(this."Property 1", "abc") → resolves to false if Property 1 contains "abc", resolves to true if Property 1 does not have "abc" in it
    STARTSWITH
    • Checks if a property or string starts with a certain value
    • =STARTSWITH("Value you are checking", "what it might start with")
    • ex: =STARTSWITH(this."Property 1", "s") → resolves to true if Property 1 starts with "s"
    ENDSWITH
    • Checks if a property or string ends with a certain value
    • =ENDSWITH("Value you are checking", "what it might end with")
    • ex: =ENDSWITH(this."Property 1", "s") → resolves to true if Property 1 ends with "s"
    BETWEEN
    • Checks if a number is between two other numbers (inclusive) → true if it IS between those numbers, and false if it is NOT between those numbers.
    • =BETWEEN("number we are checking", "lower bound inclusive", "upper bound inclusive")
    • ex: =BETWEEN(14, 12, 15) → resolves to true because 14 is between 12 and 15, inclusive
    • ex: =BETWEEN(11, 12, 15) → resolves to false because 11 is not between 12 and 15, inclusive
    • ex: =BETWEEN(12, 12, 15) → resolves to true because 12 is between 12 and 15, inclusive
    NOTBETWEEN
    • Checks if a number is not between two other numbers (inclusive) → true if it is NOT between those numbers, and false if it IS between those numbers.
    • =NOTBETWEEN("number we are checking", "lower bound inclusive", "upper bound inclusive")
    • ex: =NOTBETWEEN(14, 12, 15) → resolves to false because 14 is between 12 and 15, inclusive
    • ex: =NOTBETWEEN(11, 12, 15) → resolves to true because 11 is not between 12 and 15, inclusive
    • ex: =NOTBETWEEN(12, 12, 15) → resolves to false because 12 is between 12 and 15, inclusive
SUM
  • Gives the sum of either an array or all numbers passed in with commas.
  • =SUM(children."Property 1")
  • =SUM(1, 2, 3, 4, 5, 6, 7)
MEAN
  • Gives the mean of either an array or all numbers passed in with commas.
  • =MEAN(children."Property 1")
  • =MEAN(1, 2, 3, 4, 5, 6, 7)
MAX
  • Gives the max of either an array or all numbers passed in with commas.
  • =MAX(children."Property 1")
  • =MAX(1, 2, 3, 4, 5, 6, 7)
MIN
  • Gives the min of either an array or all numbers passed in with commas.
  • =MIN(children."Property 1")
  • =MIN(1, 2, 3, 4, 5, 6, 7)
COUNT
  • Count the number of elements you pass in (a list or a reference to a list) =COUNT(list)
  • ex: =COUNT([a, b, c, d, e, f]) → 6
  • ex: =COUNT(children) → returns however many children the current shape/space has
COUNTIF
  • Count the number of elements passed in that meet the specified condition
  • =COUNTIF([1,2,3,4,5], THIS < 3) → returns 2 (only 1 and 2 are less than 3)
  • =COUNTIF(children, THIS."Total" < 10) → returns however many of the children have a value "Total" that is less than 10
  • =COUNTIF(children, contains(THIS."Name", "Steve") → returns however many of the children have "Steve" in their name.
FILTER
  • Given a list, this filters the list down to just contain the items that match the condition in the second parameter.
  • =FILTER(list, condition to keep in the list)
  • ex: =FILTER(descendants, contains(@'Employee Name', 'Steve')) → returns a list of every descendant of the space that has the property 'Employee Name' that also contains 'Steve'.
  • ex: =FILTER(@'Accounts', @'Value' < 5000) → given that the property 'Accounts' contains all the accounts, returns on the accounts that have a value of less than 5000.
SORT
  • Given a list, this sorts the list in ascending order.
  • Optional parameters allow you to change the sort.
  • =SORT(list)
  • ex: =SORT(children) → returns a list of the children of the space, such as the page, sorted by their text, such as [Amsterdam, London, Rome].
  • ex: =SORT(downstream) → given that all the downstream shapes contain a name, returns those shape references sorted by their text (the names), such as [Amanda, Joe, Zoe].
    • Adding in the optional parameter, for sorting by ascending (A to Z, smallest value first), or by descending (Z to A, largest value first).
    • =SORT(list, 'asc') → sorts the list in an ascending order
    • =SORT(list, 'desc') → sorts the list in an descending order
    • ex: =SORT(children, 'asc') → returns a list of the children of the space, such as the page, sorted by their text in ascending order, such as [Amsterdam, London, Rome].
    • ex: =SORT(children, 'desc') → returns a list of the children of the space, such as the page, sorted by their text in ascending order, such as [Rome, London, Amsterdam].
    • ex: =SORT(downstream, 'asc') → given that all the downstream shapes contain a name, returns those shape references sorted by their text (the names) in ascending order, such as [Amanda, Joe, Zoe].
    • ex: =SORT(downstream, 'desc') → given that all the downstream shapes contain a name, returns those shape references sorted by their text (the names) in descending order, such as [Zoe, Joe, Amanda].
    • ex: =SORT(downstreamdeep.'Performance Review', 'desc') → given that all the downstream shapes contain a property called 'Performance Review', returns a list of the deeply downstream shapes' performance review scores, sorted by in descending order, such as [7, 6, 6, 5, 4, 4, 4, 3, 2].
      • Adding in another optional parameter, that comes after 'asc'/'desc', for sorting shapes by a specific property:
      • =SORT(list, order, sortBy) → sorts the list in the given order, by the given property.
      • ex: =SORT(children, 'asc', this.'City') → given that the children of the space have a property called 'City', returns a list of the children of the space, sorted by their 'City' property in ascending order, such as [Amsterdam, London, Rome].
      • ex: =SORT(children, 'desc', this.'Shipping Date') → given that the children of the space have a property called 'Shipping Date', returns a list of the children of the space, sorted by their 'Shipping Date' property in descending order, such as [London, Amsterdam, Rome] → indicating that London's shipping date is the latest, and Rome's shipping date is the soonest.
      • ex: =SORT(children, 'desc', this.'Current ARR') → given that the children of the space have a property called 'Current ARR', returns a list of the children of the space, sorted by their 'Current ARR' property in descending order, such as [Account #3, Account #8, Account#2] → indicating that the largest account is Account #3, followed by Account #8, etc.
CONTENTS
  • Reference (or retrieve) the contents of a Smart Container
  • =COUNT(CONTENTS) → returns 3 (if there are three items in the container)
  • =SUM(CONTENTS.Planned) → Returns Sum of all items Planned values inside a container
CONTAINER
  • Reference the container that contains an item in Smart Containers.
  • Used on an item in a smart container.
  • =CONTAINER → returns an item's container label
  • =IF(CONTAINER = 'Low') → Returns all item's whose container is labelled 'Low'
FILLCOLOR / BACKGROUNDCOLOR
  • Takes in a referenced object (this, parent, upstream, downstream, page, etc.) and returns the hex fill color.
  • =BACKGROUNDCOLOR(thing I want the color of)
  • =FILLCOLOR() returns the shape's fill color
  • ex: =BACKGROUNDCOLOR(page) → #FFFFFF
CURRENTSECOND
  • A counter that starts at current second in real time and counts up every second. =CURRENTSECOND
  • Resets at 60
IFERROR
  • If there is an error in the expression provided, this returns the value that you want if there is an error.
  • Purpose is to keep your document from breaking if you get an error!
  • =IFERROR(expression, value if the expression produces an error)
  • =IFERROR(parent."Total", 0) → returns 0 if, for example, this is on a shape that does not have a parent or if the parent does not have a property called "Total"