Forum Discussion

jty1969's avatar
jty1969
Copper Contributor
Oct 15, 2021

Need help with my formula, please!

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")))))

Excel on Mac
Formulas and Functions

2 Replies

  • Qaiser_j's avatar
    Qaiser_j
    Brass Contributor
    Jun 14, 2024

    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 https://chartexpo.com/utmAction/NCtNVEMreGwrUUorTVNPRlRDTVQr.

     

    And if you are comfortable with Google Sheets, you can find it https://chartexpo.com/utmAction/NCtNVEMrZ3MrUUorTVNPRlRDTVQr.

     

    Thanks

     

    Likert Scale Chart for survey data

     

    Following 2 more charts for survey.

     

     

     

  • mathetes's avatar
    mathetes
    Silver Contributor
    Oct 15, 2021

    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.

Resources