3 Replies Latest reply on Apr 26, 2011 9:27 AM by Rina Bongsu-Petersen

# Likert Scale with different scales: how to calculate the % dynamically?

I'm analyzing a survey that uses different scales: some questions have scale from 1-4, some have 1-5 up to 1-8. I'd like to calculate the % according to the scale, for example, for questions with scale 1-4, I'd like to calculate % respondents who answer 3 & 4. For scales 1-5, % respondents who answer 4 & 5, etc.

Currently, I put them into two worksheets: 1 group for questions with scale 1-4 and 1 group for questions with scale 1 - over 4. For the latter group, I calculate the % respondents who answer 4 and over.

I attached the workbook. I'd love to hear any advice/suggestions you have.

Thanks!

• ###### 1. Re: Likert Scale with different scales: how to calculate the % dynamically?

I am not sure exactly what you are looking for, but my first thought for a starting place is a calculated like:

Max Value for Question Group

```CASE [Question Group]

WHEN "Academic and Intellectual Experiences" THEN 4
WHEN "Additional College Experiences" THEN 4
WHEN "Educational & Personal Growth" THEN 4
WHEN "Enriching Educational Experiences" THEN 4
WHEN "Examinations" THEN 7
WHEN "Institutional Environment" THEN 4
WHEN "Mental Activities" THEN 4
WHEN "Problem Sets" THEN 5
WHEN "Quality Of Relationship" THEN 7
WHEN "Read & Write" THEN 5
WHEN "Same College?" THEN 4
WHEN "Satisfaction" THEN 4
WHEN "Time Usage" THEN 8
ELSE 8 END
```

that will allow for calculated fields like:

```IF [Value] >=[Max Value for Question Group]-2

THEN "Great"
ELSE "Other" END
```

• ###### 2. Re: Likert Scale with different scales: how to calculate the % dynamically?

I'd probably be inclined to have a lookup table (as a separate sheet in your original spreadsheet) which defines attributes of each question, like the range of answers and the range(s) you want to report on.  Then you can just join your responses to the lookup table and %age in high range in a single sheet.  You could then also parameterise the range to report on very easily.

• ###### 3. Re: Likert Scale with different scales: how to calculate the % dynamically?

Great suggestions. Thank you a bunch to you both !!!