Need help with my formula, please!

Copper Contributor

Trying to return a numerical value 1-5 based on the text response in the cell. It's basically an attempt to turn a Likert scale response into a number. Here's what I have so far but it's not working: =IF(J3=Not At All True, "1", IF(J3=Slightly True, "2", IF(J3=Moderately True, "3", IF(J3=Substantially True, "4", IF(J3=Very True, "5")))))

2 Replies



That kind of thing is better done as a VLOOKUP

See the attached sample sheet, where J3 is a drop down that allows for the five possible answers.

J6 contains the following formula:


and M3:N7 is a table that contains the texts and the associated values. You could change the wording in that table and have it automatically picked up in the wording of the drop down in J3.

Hi @jty1969 

I attached sample charts created on survey data. Data refresh in the chart automatically as you change your data selection.


I regularly create these charts with the following tool. Hope you will like it.


You can check here for Microsoft Excel & Office 365.


And if you are comfortable with Google Sheets, you can find it here.




Likert Scale Chart for survey data



Following 2 more charts for survey.