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 do 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
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.
    • 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 ]
      • 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.
    • This also works with the [] : =DOWNSTREAMDEEP.CONNECTED[2][3] → returns 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.
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"
LASTCALCULATEDVALUE
  • Given another label, such as @'Property 1' or page.'Total', this will give the previously calculated value - NOT the current value of page.'Total', but the previously calculated one.
  • =LASTCALCULATEDVALUE(property reference) → returns the most recent previously calculated value of that property)
  • ex: =LASTCALCULATEDVALUE(page."Total") → returns the last known value of page.'Total'
&
  • 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(10 > 15, 10, IF(15 > 12, 15, 12)) → the first if, 10 > 15, is false, so now we focus on the second if statement
          • =IF(15 > 12, 15, 12) → 15 is greater than 12, so the answer resolves to 15
      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.
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"