Extracting text from a column to find combine average

Autumn Montegna

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

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.

cheers

Kelly