Hello Smartsheet Community! As our Community grows, I am starting to see some trends about features and workarounds users want to learn more about. For these topics, I am starting a new series of announcements. In this series, I will write about trending topics and topics that you request! After I post about a subject, I can answer any questions you have or go into further detail on any aspect of the topic.
Welcome to the first of this series -- Working with Symbol Formulas
This post will teach you how to build formulas with the new symbols that were added a couple months ago. If you learn the basic syntax of building a few different formulas, you can take the same formula and change up the wording to make the formula work with any symbol type.
We will start with basic IF statements that work like this:
=IF(*this* is true, then do *this*, if not then do *this*)
=IF(logical_test, value_if_true, value_if_false)
Here’s a simple IF statement located in a Flag column that will enable (1 = enabled) the flag if the referenced Due Date is in the past:
=IF([Due Date]2 < TODAY(), 1, 0)
This same formula can be used with any of our new symbols. Let’s use the weather symbols and display the Stormy” symbol if the Due Date is in the past and Sunny” if it’s not:
=IF([Due Date]2 < TODAY(), “Stormy”, “Sunny”)
My next example is a little more complicated and will show you how formulas will basically stay the same across different types of symbols, like Progress Bars, Hearts, and Stop/Rewind/Play symbols.
We will be using nested IF statements, which are multiple IF statements combined in a single formula. This gives us more options for the formula results. Here’s how they work:
=IF(*this* is true, then do *this*, IF(*this* is true, do *this*, IF none are true, do *this*)))
=IF(logical_test, value_if_true, IF(second_logical_test, value_if_true, value_if_all_false))
Here’s what the formula will do in these examples:
If % Complete is 0%-24%, *first option*. If 25%-49%, *second option*. If 50%-74%, *third option*. If 75%-99%, *fourth option*. If 100%, *fifth option*
Progress Bar:
=IF([% Complete]2 < 0.25, "Empty", IF([% Complete]2 < 0.5, "Quarter", IF([% Complete]2 < 0.75, "Half", IF([% Complete]2 < 1, "Three Quarter", IF([% Complete]2 = 1, "Full")))))
Hearts:
=IF([% Complete]2 = 0, "Empty", IF([% Complete]2 < 0.25, "One", IF([% Complete]2 < 0.5, "Two", IF([% Complete]2 < 0.75, "Three", IF([% Complete]2 < 1, "Four", IF([% Complete]2 = 1, "Five"))))))
Stop/Rewind/Play:
=IF([% Complete]2 < 0.25, "Stop", IF([% Complete]2 < 0.5, "Rewind", IF([% Complete]2 < 0.75, "Play", IF([% Complete]2 < 1, "Fast Forward", IF([% Complete]2 = 1, "Pause")))))
If you can learn how to build formulas for one type of symbol column, you can build formulas for any symbol type. The trick is just learning which terms are related to what symbol. An easy way to do this is to add the symbol column to your sheet and click the dropdown arrow in one of the cells to display all the options and the wording or by checking out this Help Center article on the different symbol columns available.
