Working with Symbol Formulas

Travis

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. 

 

Charles DESCOTES

Your post will brilliantly help me teaching Smartsheet within my company.

I keep telling my people that your Help library is like the Niebelungen Treasury!

Lily Wang

Thanks for this post. It is really useful. 

 

I'm building a smartsheet which has 4 symbols - red, yellow, green, grey.  The first three are to indicate the various stages of a project.  So far, the formula that I'm using is =IF(CPI_1>1, "Green", IF(CPI_1 = 0, "Yellow", IF(CPI_1 < 1, "Red"))).

 

 

But I would like the grey symbol to be show up automatically for the CPI_1 cell only if there is no data in that cell.  What should I write in the formular?

Charles DESCOTES

=IF(ISBLANK(CPI_1), "Gray", IF(CPI_1 >= 1, "Green", IF(ABS(CPI_1) < 1, "Yellow", IF(CPI_1 <= -1, "Red"))))

Lily Wang

Thank you so much George!

 

Sorry I have a follow up question...  Some cells in the CPI_1 column shows #Blocked.  I'd like those cells to also show up as gray. Do you know if there's such a way? 

Charles DESCOTES

Sorry, I couldn't do it. You would have to sort out the issue upstream.

 

From SmSh FAQ:

Q. How can I prevent my formula from returning an error message?

A. Smartsheet doesn't currently have any automated functionality that would suppress error messages caused by a formula.

 

From SmSh explaining error messages:

#BLOCKED

Cause: The calculation is blocked because at least one of the cells referenced by the formula has an error.
Resolution: Determine which cell referenced by this formula contains an error, which will be more descriptive of the problem.

 

More on

http://help.smartsheet.com/customer/en/portal/articles/775363-using-formulas

Darrin Phillips

How do I use the "if" formula with the cell signal symbol? I can't seem to get it to work using %.

 

Crossmark

How about formulas based on symbols? Like, "If Field1 has 2 stars, let Field2 = 2".

Crossmark

Solution! 

I wanted to assign numeric values to symbols used in several columns, then tally those to calculate a score.

Here's a score for the a 5-star rating field, with no stars equaling the highest value, and more stars equaling a lower value:

=IF([Time Involved]182 = "Empty", 6, IF([Time Involved]182 = "One", 5, IF([Time Involved]182 = "Two", 4, IF([Time Involved]182 = "Three", 3, IF([Time Involved]182 = "Four", 2, IF([Time Involved]182 = "Five", 1, 0))))))

And for those single-star symbols that toggle on/off, I used a simple calculation that returns "0" if the star is off and "5" otherwise: 

=IF(Important140 = 0, 0, 5)

 

TodWulff

Hola.  As info, I found that the built-in symbol support was a bit restrictive for some of our needs.  As such, I looked into using Unicode Characters, coupled with formulas and conditional formatting, and was able to implement solutions that were more robust than that currently offered by the stock SmartSheet functionality. 

Here is a quick snip from a dev sheet I was working up:

• Note the additional ball colors and other characters that convey state pretty intuitively.  There are a bunch more characters than these.  I just used these as a quick example.

Use Case Example:  

I've implemented, as part of a PM tool, a combined Schedule/Accomplished 'dashboard' Status cell for each line item or deliverable (background color represents Schedule state, whereas foreground character and color represent Level Of Effort/Completeness).

• Here is the 'Legend' and some early pseudo code:

• Here is snippet from a SmartSheet in production use:

• To Do:  Add column for 'Effort' (man-hours) and then weight each deliverable against the overall effort to fine tune the schedule (fill color) and effort indicator (char & foreground color) on the Status cell.

For more information on Unicode Character use, one can review this page, and the sample sheets linked to therein:  https://community.smartsheet.com/discussion/unicode-text-symbols-are-extremely-useful

When coupled with conditional formatting and formulas, this helps to further open up the utility of your SmartSheets.

Hope this helps.  Take care.

-t

Mikey

Hi All,

I'm trying to write a formula in the % complete column.

I want to use the 'progress pie' symbols - i.e. if circle is empty, % complete should be 0%, if circle is a quarter full, % complete should be 25%, if circle is half full, % complete should be 50% and so on.

Is this possible, or does the % complete column not accept formulas?

Appreciate any guidance you can give guys.......

Charles DESCOTES

"% complete" as a column cannot stand formulas when it is  set as ... the % complete column for the Gantt (look up in Project Settings (cog wheel at the top right of the Gantt chart)

If you double click on the column header, it will also tel you so.

Pity indeed

Mikey

Many thanks Charles - I found a work-around from another thread where you set up a new column containing your formula, link the results into another sheet and then link them back into the %complete column. Works ok, but really?!

Thanks again,

Mike

sarshad

Is there a reason I am seeing the text and not the symbols? The same formula works fine in another sheet. Is this got to do with a setting?

Charles DESCOTES

They are case sensitive

Type "Full" instead of "full" etc.

Barry Bowles

Hi Mikey - you may have found this solution by now - I'm not sure if its a recently added feature to SS but you can use a formula in the % complete column by disconnecting it from use in the Gantt chart. The setting is under Project Settings/Dependency settings and you set the % Complete column option to "none". That means you will not see a progress bar in the Gantt chart but my guess is you are more interested in getting your progress showing visually in the grid view.

Hope this helps.