Extracting text from a column to find combine average

Hello and thank you for your help!

I have three "select one" dropdown columns that have rubric scores for questions, e.g.

1 - no applicable experience

2 - minimal experience

3 - some ability

4 - above average

5 - high level

Is there a formula to just extract the score (or number) selected in each column and find the average?

For instance, if Column A score = 2 - minimal experience, Column B score = 4 - above average, and Column C score = 3 some ability, is there away to find the average of 3 in it's own column?


  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Autumn Montegna

    Try this

    =AVG(VALUE(LEFT([your 1st column]@row, 1)), VALUE(LEFT([your 2nd column]@row, 1)), VALUE(LEFT([your 3rd column]@row, 1)))

    Please be sure to change the column names to match your columns. The VALUE function forces the text to become a real number. The LEFT function is a way of extracting a specific number of characters, beginning from the left. The '1' in the formula above says grab only the first character.



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!