Convert Symbols to Numerical values

Scott Verduin

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

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

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:

https://help.smartsheet.com/learning-track/smartsheet-intermediate/sheet-summary

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.

Thanks!!

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!

Best,

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!