How to use formulas

Read on to learn how to use formulas on Lucidpress.

Instagram_-_Untitled_Page.png

Lucidpress rounds out its table data capabilities by offering formulas to users. Much the way spreadsheet tools allow users to analyze, adjust, and quantify their data, Lucidpress allows users to run certain formulas on their documents.

Data can be added to tables in various ways, including linking data from spreadsheets, or adding custom data. Custom data can contain static values, or formulas. 

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 Lucidpress
  • Keyed Values: Shape data that is reference by a Reference Key
  • Parent: The Lucidpress 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:

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


Other Background Information

All formulas start with =

  • And {{= when typing directly in the shape


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
  • 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 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.
    • 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].
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"
 
 
Was this article helpful?
0 out of 0 found this helpful