Skip to main content

Hey Lucidchart Team -


I have a formula in my document that looks like this..


=UPSTREAM(UPSTREAM).Sequence + 1


When not connected to anything upstream it produces the following result #ERROR


When not adding the "+1" I get an empty result.


=UPSTREAM(UPSTREAM).Sequence produces []


I would like to use this in an IF statement to test for the empty result so that I can avoid the #ERROR and default my sequence to 1.


=IF(UPSTREAM(UPSTREAM).Sequence = [] 1 UPSTREAM(UPSTREAM).Sequence + 1) produces #ERROR


I've created a document that illustrates it: https://www.lucidchart.com/invitations/accept/9ba2f524-f97a-46bb-b228-33f92d3547bc


Cheers


Michael

Hi Michael


Thank you for building out that document and for sending it to me! It illustrates really well what you are trying to do and your thought process on how you can best achieve it. As you highlighted "[ ]" is not recognized as a viable option for potential upstream values and because of this it throws an error message when incorporated in the IF function. 


There are a couple of ways that you could work around this limitation in your document.



  1. You could set one "master" upstream shape with a sequence value of 0 that all shapes not connected to any others can have that as their upstream shape. This shape could be transparent with transparent text so as not to ruin the visual flow of your document. This is not ideal but would likely work...

  2. You could set up all of the formulas and tracking for these objects in a Google Sheet and link that data into your chart using our Data Linking feature. This would allow you to calculate out all of the numbers before adding them into your shape and anytime a modification was made to the sheet it would automatically populate into Lucidchart.

  3. It is possible that there is another combination of functions or formulas that could be used in the chart that you have already built to provide the functionality you are hoping for. I was not able to find a satisfactory workaround in this category but I would encourage you to spend some time going over this help center article which covers all of the formulas and functions supported in Lucidchart in detail. 


I'm sorry that I wasn't able to find a more straight-forward answer and hope that one of these options allow you to continue your work. Let me know if you have any additional questions or concerns!


Reply