How to Use Formulas

Read on to learn how to use formulas on Lucidchart.

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

Personal & Family Relationships (Reference Lookup Functions)

THIS

  • Used when you want information/shape data about the selected shape.
  • Used by itself:

    • =THIS
    • Returns a string
    • In the shape data panel, =THIS gives the text (concatenated if multiple text fields) and fill color of the selected shape
      • Note: Groups do not have text or fill color, so =THIS will return Empty

        this.png
    • On a shape, =THIS still tries to give the text on that shape, which is =THIS, which causes a circular dependency and everything breaks. Don't do that.

      badthis.png
  • Dereferenced:

    t1.png

    • =THIS."Collection Name"
      • Returns all shape data values attached to that shape
      • Ex. =THIS."MyData" produces [A01, John, Jacobs, 47]
    • =THIS."Label Name" or =THIS."Collection Name"."Label Name"
      • Returns a single shape data label or value
      • With keyed data: =@"Age" or =@"MyData"."Age" produces 47
      • With custom data: =@customdata."Birth Year" produces 1971
      • Keyed data takes preference over custom data. If custom data and keyed data have the same label, THIS will return the value (or an array or values) of the keyed data

        4.png
    • THIS can be used as an input in other formulas
      • For example,

        g2.png

        =PARENT(THIS)."Parent Group" produces Hello World!



PARENT()

  • Used when you want information/group data about the group to which the select shape belongs
    • NOT upstream/downstream or associated with any arrow direction
  • Used by itself:

    • =PARENT
    • Like =THIS, =PARENT tries to return the text on the group "shape", but groups do not have text.
    • However, if it is used on a shape that is not inside a group, =PARENT will return the name of the page.
  • Dereferenced:

    • =PARENT."Collection Name"
      • Returns all shape data values attached to the group
    • =PARENT."Label Name" or =PARENT."Collection Name"."Label Name"
      • Returns a single shape data label or value of the group
    • Ideal for grouped custom shapes
      • Users can create grouped custom shapes where elements have PARENT formulas in them. When the user drags a new row of data onto the grouped shape, the PARENT formula makes the text in the individual elements populate and prevents the user from having to drag the row to each individual element in the group.
      • For example,

        6zATVv1.gif

        Ex. =PARENT."First Name" produces John
  • Note: The "highest", "most senior", "topmost", etc. parent is the page. If an element is not in a group, PARENT will produce page data.



CHILDREN()

  • Used when you want information/group data about all elements of a group (both shapes and lines)

    • NOT upstream/downstream or associated with any arrow direction
  • Used by itself:

    • =CHILDREN
    • Similar to THIS, returns an array of the text on all shapes in a group (in the order the shapes were added to the canvas)

      bI5LoH7.png
  • Dereferenced:

    • =CHILDREN."Collection Name"
      • Returns all shape data values attached to any of the elements in a group
    • =CHILDREN."Label Name" or =CHILDREN."Collection Name"."Label Name"
      • Returns an array of the shape data label or value of the elements in a group that match that label
    • Ideal for applying formulas to a group of objects
      • For example, counting children in a group and finding their average age

        ezgif.com-crop.gif

        • Ex. =CHILDREN."Age" produces [47. 20, 35. 52]
        • Ex. =COUNT(CHILDREN) produces 4
        • Ex. =MEAN(CHILDREN."Age") produces (47+20+35+52)/4 = 38.5



DESCENDANTS()

  • Used when you want information/group data about all elements of a nested group
    • NOT upstream/downstream or associated with any arrow direction
  • Used by itself:

    • =DESCENDANTS
    • Similar to THIS, returns an array of the text on all shapes in a nested group (in the order the shapes were added to the canvas)
      • The internal groups will be represented by an empty string

        xeP6lPM.png xLc68yl.png
  • Dereferenced:

    • =DESCENDANTS."Collection Name"
      • Returns all shape data values attached to any of the elements in the group or its subgroups
    • =DESCENDANTS."Label Name" or =THIS."Collection Name"."Label Name"
      • Returns an array of the shape data label or value of the elements in that group or any group that match that label
    • Used similarly to CHILDREN
      • Note: =COUNT(DESCENDANTS) will count both the number of shapes and the number of groups



UPSTREAM()

  • In Lucidchart, arrows have directionality. UPSTREAM, gets all of the lines that end on the current selection—DOWNSTREAM is the opposite.
  • You can tell directionality of lines by the indicators in the option bar. The start of the line has the point indicated in the first input, and the second input indicates the endpoint. The swap button switches the directionality of the line.
  • Used when you want information/group data about shapes upstream of the selected shape
    • In A → B, A is upstream of B
  • Used by itself:

    • =UPSTREAM
    • Similar to THIS, returns an array of the text on all upstream shapes (in the order in which the lines were attached)

      oNT7Xfj.png
    • Note: If two shapes are connected with multiple lines, they will only be "counted" once in the formula
  • Dereferenced:

    • =UPSTREAM."Collection Name"
      • Returns all shape data values attached to any of the upstream elements
    • =UPSTREAM."Label Name" or =UPSTREAM."Collection Name"."Label Name"
      • Returns an array of the shape data label or value of the upstream elements that match that label
    • Ideal for identifying possible bottlenecks in a process
      • For example, suppose Process C can only accommodate 10 units of raw material at one time. Since Process A and Process B flow into Process C, if they supply more than 10 units combined, Process C will not be able to keep up.

        ezgif.com-crop__1_.gif
      • Ex. =SUM(UPSTREAM."Units") produces 'units from A' + 'units from B' = 5 + 6 = 11



DOWNSTREAM() and CONNECTED()

  • Similar to UPSTREAM

    3CfpT3Y.png

  • In A → C → B, A is upstream of C, B is downstream of C, and both A and B are connected to C.


All of the relationship functions can take, as a parameter, the result of another relationship function. For example, =PARENT(PARENT) would get the grandparent, or =CONNECTED(CONNECTED) would get all items connected to the items connected to the selected item.


Metadata Functions

LABEL()

  • Used to find the unique Lucid shape ID
  • Similar to the CELL function in Excel with first variable "contents"
  • Used with THIS:

    • =LABEL(THIS)
    • Returns the unique Lucid shape ID as a string (ex. 1g.AA~NDkjAT )
    • =LABEL(THIS."Label")
    • Returns the label name of the shape data label value pair with label "Label"
  • Used when you want to reference data attached to a specific shape

    • =LABEL(THIS) produces a unique ID for that shape
    • =#'unique ID'."Shape Data Label" produces the shape data value with of that shape with that label
    • Ex.

      sFvH64B.pngimageedit_2_5807292044.png


Lookup Functions

LOOKUP()

  • Used for finding data using a reference key
  • Similar to Excel's LOOKUP (VLOOKUP,HLOOKUP ) functions
  • Used by itself:

    • =LOOKUP("Collection Name") returns "Collection Name"
    • =LOOKUP("Collection Name", "Reference Key") returns an array of all data associated with that reference key
  • Dereferenced:

    • =LOOKUP("Collection Name", "Reference Key")."Shape Data Label"
      • Returns a value from an array of the shape data within an array
    • =LOOKUP("Collection Name", "Reference Key")."Shape Data Label" returns the shape data value associated with that reference key and that shape data label
  • Ideal for finding data associated to a specific reference key

    • May also be used in a similar way as Excel's VLOOKUP
      • Ex.

        bbbbbbbbbbbb.png
      • =IF(LOOKUP("MyData", "A02")."BB"="Alice", "Yes", "No")



FIELDLOOKUP()

  • Used for finding data using a reference key
  • Similar to LOOKUP but only used to find one shape data value at a time
  • Used by itself:

    • =FIELDLOOKUP("Collection Name", "Shape Data Label", "Reference Key")
    • Ex. =FIELDLOOKUP("MyData", "BB", "A02") is the same as =LOOKUP("MyData", "A02")."BB"
  • Not dereferenced



Boolean Functions

AND()

  • =AND(Input 1, Input 2, ...) evaluates to true if all inputs are true
  • Similar to Excel's AND function


OR()

  • =OR(Input 1, Input 2, ...) evaluates to true if any of the inputs are true
  • Similar to Excel's OR function


NOT()

  • =NOT(Input) reverses the boolean value of the input
  • Similar to Excel's NOT function
  • Used to make sure one value is not equal to another
    • Ex. =IF(NOT(BACKGROUNDCOLOR(THIS)="#ffffff"), "This is not white", "This is white")


IF()

  • =IF(Test condition, Output if true, Output if false) tests whether the first input is true and returns a value based on the result
  • Similar to Excel's IF function



Mathematical Functions

SUM()

  • =SUM(Input 1, Input 2, ...) adds all the inputs together
  • Similar to Excel's SUM function


PRODUCT()

  • =PRODUCT(Input 1, Input 2, ...) multiplies all the inputs together
  • Similar to Excel's PRODUCT function


MEAN()

  • =MEAN(Input 1, Input 2, ...) takes the arithmetic mean of the inputs
  • Similar to Excel's AVERAGE function


MAX()

  • =MAX(Input 1, Input 2, ...) returns the value of the greatest input
  • Similar to Excel's MAX function


MIN()

  • =MIN(Input 1, Input 2, ...) returns the value of the smallest input
  • Similar to Excel's MIN function


COUNT()

  • =COUNT(Input 1, Input 2, ...) counts the number of inputs
  • Similar to Excel's COUNT function
  • Good for counting the number of shapes on a page with existing shape data
    • Ex. =COUNT(CHILDREN."Shape Data Label")


Related Articles
Link Lucidchart Diagrams to Spreadsheet Data
Create Graphs from Linked Data
Using the Equations Library
Conditional Formatting and Icon Sets