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

@jty1969 

 

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:

=VLOOKUP(J3,M3:N7,2,0)

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.

 

Thanks

 

Likert Scale Chart for survey data

Qaiser_j_0-1718360030445.png

 

Following 2 more charts for survey.

Qaiser_j_1-1718360080437.png

 

Qaiser_j_2-1718360091758.png