# Convert Symbols to Numerical values

Options

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

Tags:

• ✭✭✭✭✭✭
Options

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

• Options

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

• ✭✭✭✭✭✭
Options

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:

• Options

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

• ✭✭✭✭✭✭
Options

Hi Scott,

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | 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!