Working with Symbol Formulas (2024)

Options

Travis Employee

05/19/15 edited 04/05/24 in Best Practice

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.

Tags:

Formulas

8 · Share on FacebookShare on Twitter

«12»

Charles DESCOTES ✭
05/22/15

Options
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!

1 · Share on FacebookShare on Twitter
Lily Wang ✭
09/10/15 edited 09/10/15

Options
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?

0 · Share on FacebookShare on Twitter
Charles DESCOTES ✭
09/10/15

Options
See Also
Formula Basics | Smartsheet Learning Center How To Use If Statements In Smartsheet IF STATEMENT WITH A RESULT IN SYMBOL FAQs: Using formulas | Smartsheet Learning Center
=IF(ISBLANK(CPI_1), "Gray", IF(CPI_1 >= 1, "Green", IF(ABS(CPI_1) < 1, "Yellow", IF(CPI_1 <= -1, "Red"))))
0 · Share on FacebookShare on Twitter
Lily Wang ✭
09/13/15 edited 09/13/15

Options
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?

0 · Share on FacebookShare on Twitter
Charles DESCOTES ✭
09/14/15

Options
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

0 · Share on FacebookShare on Twitter
Darrin Phillips ✭✭
01/09/17

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

0 · Share on FacebookShare on Twitter
Crossmark ✭✭
06/03/17

Options
How about formulas based on symbols? Like, "If Field1 has 2 stars, let Field2 = 2".
See Also
How to setup an "If" with dates involved?
0 · Share on FacebookShare on Twitter
Crossmark ✭✭
06/03/17

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

0 · Share on FacebookShare on Twitter
TodWulff ✭
02/15/18 edited 02/16/18

Options
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 openup the utility of your SmartSheets.
Hope this helps. Take care.
-t
2 · Share on FacebookShare on Twitter
Mikey ✭✭✭✭
08/13/18

Options
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.......

0 · Share on FacebookShare on Twitter
Charles DESCOTES ✭
08/14/18

Options
"% complete" as a columncannot 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

0 · Share on FacebookShare on Twitter
Mikey ✭✭✭✭
08/14/18

Options
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

0 · Share on FacebookShare on Twitter
sarshad ✭✭✭
05/17/19

Options
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?

0 · Share on FacebookShare on Twitter
Charles DESCOTES ✭
05/20/19

Options
They are case sensitive
Type "Full" instead of "full" etc.

0 · Share on FacebookShare on Twitter
Barry Bowles ✭✭✭✭
06/02/19

Options
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.

0 · Share on FacebookShare on Twitter

«12»

'); $(this).css('min-height', '20px'); const searchBoxRoot = document.getElementById("searchbox"+index); Coveo.initSearchbox( searchBoxRoot, "https://search.smartsheet.com/community/" ); }); $('#titleBar .CoveoSearchbox').hide(); $('.coveoSearchButton').on("click",function(){ $('#titleBar .CoveoSearchbox').toggle(); }); // ====== Front Page Search Box ====== $('#Form_search').remove(); var boxes = ["#search-0", "#search-1", "#search-2", "#search-3", "#search-4", "#search-5"]; console.log("PT: Before search box replacement"); $(boxes).each(function(i, val) { // ====== Front Page Search Box ====== $(val).each(function( index ) { console.log(this); $('#searchbox' + i).remove(); $(this).find('form').remove(); $(this).addClass('fpsearchbox'); $(this).attr('id', 'fpsearchbox' + index); $(this).append('

'); $(this).css('min-height', '20px'); const searchBoxRoot = document.getElementById("fpsearchbox"+index); var action = "https://search.smartsheet.com/community/"; if ( $('body').hasClass('Categories')) { var category=$('h1.heading-1').text(); if (category === "Smartsheet Product Feedback & Ideas") { category = encodeURIComponent(category); Coveo.$(searchBoxRoot).on('buildingQuery', function(e, args) { args.queryBuilder.advancedExpression.addFieldExpression('@communitycategory', '==', [category]); }); action = action + '#&f:@communitycategory=['+category+']'; } } Coveo.initSearchbox( searchBoxRoot, action ); }); }); });

Working with Symbol Formulas (2024)

Categories

References