Using If formula with symbols

Hi there,

Trying to create a formula using symbols, but am getting the #UNPARSEABLE message.

Under my Progress column, I'm using symbols (Red, Green, Yellow and Blue).

For the Value if true: if the symbol is green, then I want the number that is entered into the column titled "Run Time" to be populated into "Column 12". For example, if a "5" is entered in Run Time and the symbol is green, I want the 5 to be populated in Column 12.

For the Value if false: if the symbol is any other colour, then I want the number entered into "column 12" to be zero.

This is the formula I've put in but it doesn't seem to be working:

=IF([Progress]4 = 3, "=([Run Time (in Hours)]@row)", "0"))

I'm pretty sure that the 3 should correspond with the green symbol.

First time working with this type of formula - Any ideas? I'm all ears :)

Hiya! 🙂

Just jumping in to clarify here - @Jason Nairnsey is correct! You'll need to spell out the colours as text in your formula and that article will identify the correct text syntax.

However I also wanted to call out the second half of your formula where I see you have a number of quotes:

=IF([Progress]4 = 3, "=([Run Time (in Hours)]@row)", "0"))

Since you're referencing a cell and then a number, neither of these elements need quotes. Try something like this:

=IF([Progress]@row = "Green", [Run Time (in Hours)]@row, 0)

Cheers,

Genevieve

• edited 12/02/22

I think you need to use the actual words, rather than convert to numbers, ie "Red", "Green", "Yellow" and "Blue". See this article for more details:

https://help.smartsheet.com/articles/2480316-available-symbols-in-symbols-column

Hiya! 🙂

Just jumping in to clarify here - @Jason Nairnsey is correct! You'll need to spell out the colours as text in your formula and that article will identify the correct text syntax.

However I also wanted to call out the second half of your formula where I see you have a number of quotes:

=IF([Progress]4 = 3, "=([Run Time (in Hours)]@row)", "0"))

Since you're referencing a cell and then a number, neither of these elements need quotes. Try something like this:

=IF([Progress]@row = "Green", [Run Time (in Hours)]@row, 0)

Cheers,

Genevieve

• Hi there, thanks very much! works!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!