Convert Symbols to Numerical values


What is the formula to convert the bar symbols of Empty, Quarter, Half, Three Quarter, Full to numbers?

For example,

Empty = 0

Quarter = 1

Half = 2

Three Quarter = 3

Full = 4


  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Are you wanting to convert the status into a new column? If so add this formula into a new column.

    =IF([Symbol Column Title]@row = "Empty", 0, IF([Symbol Column Title]@row = "Quarter", 1, IF([Symbol Column Title]@row = "Half", 2, IF([Symbol Column Title]@row = "Three Quarter", 3, IF([Symbol Column Title]@row = "Full", 4)))))

  • Scott Verduin

    Thanks Mike, that formula worked.

    Now I am trying to average these numbers in the column. My problem is when a web form entry comes in and add a new row as it should, the average formula does not auto adjust to the new row at the top of the sheet.

    Meaning my current average formula is =AVG(Value2:Value15). When a new row is add it adds it at the top of sheet. I want the formula to auto change to =AVG(Value1:Value15). The bottom value will auto change from 15 to 16, 17 and so forth as rows come in. I need the top value to auto change..

    Thank you

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Hi Scott,

    That currently isn't possible in smartsheet. Do you have another column where you can average the numbers in? You can use an entire column range =AVG(VALUE:VALUE) will average the entire column and catch everything, even when new items come in. Sometimes I will create a column just for this purpose to add all of my roll-up data.

    Do you have access to, or have you ever used the sheet summary panel? That panel let's you add formulas and cells that live outside your sheet, kind of like a roll-up section where you can do some of the functionality you might be trying to do. For more on the summary panel see:

  • Scott Verduin

    Hello Mike,

    I created another column and add the formula you indicated. Works perfect when new rows come in.

    I am not family with the sheet summary panel. It is confusing to me. I need to study it more.


  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi Scott,

    @Scott Verduin

    Another option depending on your structure and need for other metrics, could be to create a so-called Master Metric Sheet.

    I hope that helps!

    Have a fantastic week!


    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    ✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: | | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!