[Solved] Adding numeric values to text answers in a survey

[AgoSolvo]

I am working on a survey, and have the question in one cell, and the answer in the next cell (answers are limited to "Not Applicable", "fails expectations", "meets expectations", "exceeds expectations" using the Data>Validity>Allow Lists option).

The idea is to compute a "points earned" compared to "points possible" percentage score(N/A wouldn't count toward either earned or possible.)

Is there a way to tell a formula in a cell that a certain text input has a defined numeric value? or a way to tell a cell that is receiving text input, to instead store a numeric value(while still displaying the text)?

[FJCC]

You can translate the text entries to numeric values with the VLOOKUP() function. In some convenient place make a list of your four possible text answers with their numeric values in the next column to the right. Let's say you do this in cells Y1:Z4. For a text response in cell B2, put this formula in C2

Code: Select all

=VLOOKUP(B2;Y1:Z4;2;0)

That will look for the exact match of B2 in the cells Y1:Y4 and return the value stored in the adjoining Z cell.

[AgoSolvo]

Thank you, that worked perfectly. I had seen vlookup as a suggestion on some other posts, but the syntax for using wasn't laid out as clearly as you put it. again, thank you.