# How to calculate likert scale in excel

It only takes a minute to sign up. So I made a questionnaire using Likert Scale. Let's say that I want to know "user's satisfaction of new web interface", where I used 5 questions with Likert Scale to answer that one question.

The amount of sample is 40 and the example of the item is like this:. Because I have not much time and my knowledge in Statistics is very low, I thought of using the simple and most basic one to analyze the data. I read here and there, and my understanding is that since Likert is ordinal, don't use mean, use mode and median.

Now what I'm confused is, what those mode and median means? All the source said something about use mode or mean or median of frequency etc, but what is it that they represents? For example, say the mode of question above is 4, and the median is 3. So what it is that they say? But then how to relate this to other question result? And also, if frequency for 4 is 15 and frequency for 3 is 12, there's a lil difference between them, not sure I can say "most" user agrees When I couldn't find it, I've tried searching for chi-square and t-test, but not just I don't really understand why one use one, I can't find any example either.

First thing: If you are going to somehow average or combine responses to multiple questions, you are assuming that answers to all questions all measure the same underlying latent underlying variable e. If these answers to these questions are not all related e. Typically when people create a new questionnaire, they examine whether the new questionniare is indeed useful reliability, consistency, validity, etc.

For example, check out:. For questionnaires using the likert scale that have passed these measures, I have published papers in academic journals using the sum of the answers or mean with statistics such as regression performed on these values.

If we assume that your questionnaire has been designed correctly and that all the items measure some latent factor about "user's satisfaction of new web interface", then a mean of, say, 4.

In other words, the mean of all the scales is the value of the latent variable "user's satisfaction with the new web interface" for that person. If certain assumptions are met e. Typical cutoffs for thinking that this approximation will be acceptable is a minimum of 4 and preferably 8 items in the sum. Sign up to join this community. The best answers are voted up and rise to the top. Home Questions Tags Users Unanswered. Asked 5 years, 3 months ago. Active 5 years, 3 months ago. Viewed 22k times.

The amount of sample is 40 and the example of the item is like this: 1 I'm comfortable with the new interface. Konayuki Konayuki 1 1 gold badge 1 1 silver badge 3 3 bronze badges.Chances are that you landed on this page looking for information on Likert scales and averages.

If that is the case, you will probably be want to skip directly to the part of this post where I talk about a common mistake people make with ordinal data and mean values. You should also take a look at the list of additional resources. This post has been prompted by an edited collection that I was recently asked to review. Substantive comments on the book have been published elsewhere; but what I want to do in this post, instead, is share some thoughts regarding a common statistical mistake and a common misconception about published works.

Those who are not familiar with the fascinating minutiae of quantitative research can find a discussion of Likert scaling and ordinal data in the section that immediately follows. Those of you who are unlucky enough to have studied statistics may want to skip to the next section.

Indicate what you think about the following statements using the scale below: 1 Strongly Agree; 2 Agree; 3 Neither agree nor disagree; 4 Disagree; 5 Strongly Disagree. Each of these items measures a variablei. Sometimes, sets of similar items are dispersed in the same questionnaire. I will not go into any of this in more detail here, but if you want to find out more, this post has some additional information.

Likert scales are very frequently used to measure constructs like satisfaction rates, attitudes towards different things, and more. They are very flexible and very useful, provided you use them carefully. Likert items and scales produce what we call ordinal datai. In the example above, people who select response 1 to item d are more fond of fish fingers and custard than people who choose responses 234 and 5.

People who choose response 2 like this snack more than those who choose responses 34 and 5and so on. In addition to being ranked, ordinal data can be tallied: for example, I might want to divide by sample by age group, count how many people chose each of the responses, and compare results across ages. Imagine that ten survey participants were asked about their attitudes towards fish fingers and custard.

The table below shows a hypothetical distribution of answers:. To do this, I might use the following formula:. The mathematical model needs these assumptions in order to work, but they are simply not in the questionnaire design. And even if we forced them into the questionnaire, that would constitute a gross distortion of psychological attitudes and the social world to fit our statistical mould. To put it in the simplest terms possible: Ordinal data cannot yield mean values.

If you think that they can and some statistics guidance websites might encourage you to think soyou can still take your chances. But please make sure you justify your choice well when you write up your methods section.

(18E) Analysis of Likert Data

The median is a type of average value, like the mean, except that it shows the number that is exactly in the middle of the data, i. As I wrote at the beginning of this post, one of the papers in the volume that I reviewed made the statistical mistake that I just described, namely it described a set of findings that had been generated by extracting mean values from Likert items.

Unfortunately, this problem had gone unnoticed by the editors of the collection, and by the peer-reviewers employed by the press. As the book had already been published, I was left wondering whether there was anything to be gained by flagging it at this stage. Readers often take it on faith that the people who conducted a study knew what they were doing. This faith is sometimes misplaced. It is the nature of the peer-review process that the people who review academic articles can make intelligent substantive judgements on the findings, but might not always have the requisite background to comment on the research process or visa versa.

For better or for worse, research methods are too diverse and too specialized for reviewers to have more than a passing acquaintance with most of them. In addition, there are limits to the time one can reasonably spend providing unpaid service to the profession, and these often preclude reading up on research methodology every time one comes across a novel research design. Every now and then, reviewers have to take it on faith that the people who conducted a study knew what they were doing, and they must trust that there are no major flaws in the methods.

So, rather than double checking on such matters, we tend to focus our feedback on more substantive aspects of the research e.So for example, all those who select a 4 i. Similarly all those who select a 5 i. The median is then calculated from. Step 1 — The middle point is calculated by dividing the total number of responses by 2.

Step 2 — The category in which this middle point falls is identified. In this case Step 3 — As there are 23 respondents up to category 4, the difference between the middle point i. So As there are 26 respondents the fraction 1. This is then added to 4. I want to thank you for posing this problem.

It is very rare that I get to used my statistics education in my current role.

### How to summarise Likert scale data using SPSS

With that said, Look at the attached workbook. I believe it solves exactly your problem.

Set 1 is the set of values you gave and Set 2 is a value I made up. My blog is referenced in my signature. Let me know if there's any way I can credit you. Become a Viz Whiz on the Forums! Support the Community and master Tableau. Error: You don't have JavaScript enabled.

This tool uses JavaScript and much of it will not work correctly without it enabled. Please turn JavaScript back on and reload this page. Please enter a title. You can not post a blank message. Please type your message and try again. The median is then calculated from these transformed responses. The following example shows the steps to calculating the median.Thanks for the tip on how to create a Likert scale in Excel. In following your instructions, I found that more than one choice could be made within a group.

This only happened intermittently and even when I began with making the grouping box first, Excel still allowed more than one cell to be checked. Any suggestions?

Great how-to. A tip for aligning the boxes and spreading them equally over an area is to use the Align function found in the Arrange group. Using Align you can distribute each radio button horizontally or vertically with ease.

You can also make sure everything is centered by using the other align function found on the drop-down. Hello this is great. By far the best explanation i can find explaining this topic.

### How to Analyze Likert Scales

But I was stuck at your " Radio buttons" as I use excel I couldn't get to the "controls" button when I right click the toolbar. I was just curious if you could explain how I can report this another page by using a code? Thank you. Post a Comment. I've spent the last two posts harping on about the advantages of doing surveys and experiments using Excel and VBA. It might be time to actually get into it.

Your typical social science survey usually employ more than one of the following response modes: Likert scale Continuous scale measure Free response Multiple choice response where you can only choose one of several options Multiple options where you can choose more than one of several options I'm not going into what you should use for your study.

I'm just going to show you how each one can be easily presented and captured using Excel in the following entries. How to record a response.This post will give you some advice about using SPSS to summarise data that were generated with a Likert scale. If you want to read up on Likert scales before you go on, you can find some information in this post.

Elsewhere in this blog, I have written that a Likert scale might consist of several overlapping items. We can do this in two ways: adding the data or estimating the median. In this post, I will show you how to estimate the median, because this is slightly harder. The same steps can be modified to add up the data. I will also assume that you have already established that your scale is internally consistentso I will focus only on the technical aspects of merging the variables.

You should now see the following dialogue box. SPSS will automatically generate a new variable, which will appear at the end of your dataset. This will be in numerical form 1, 2, 3, …but you can change it to a verbal descriptor for consistency Figure 3. You can use this variable for descriptive statistics e. You may also want to check out some more posts I have written on quantitative research, including:. If you landed on this page while preparing for one of your student projects, I wish you all the best with your work.

Also feel free to ask any other questions you may have, using the contact form. Hi, I am doing likert scale questions. However, I use 2 questions under one category as the situation you shown above, there are 3 questions under one category, which is able to calculate the new variable through median.

## Getting weighted averages with the Likert question

However, for my situation, if I use median, there will be decimal places for the number. How should I do? Please help me. Thank you so much. Not a problem — when there is an odd number of data points, there will be decimals in the median. Hi, As the situation you shown above, you are using three questions for one variable, therefore you calculate median, however for my situation, I am using two questions for one variable, if i calculate median will be not approprate?

I am really do not know how should I do the analysis? Thanks for the post. But a quick question. How do I create verbal descriptions if I gave a decimal value? A less controversial thing to do is to calculate the median of the scale.

I have 5 different questions for identifying the mood or response of 1 independent variable. Now I want to compute the 5 questions having 5 tables into 1 table for my independent variable All questions are in ordinal measure.

Here im confused as to what should i take??? I prefer using the median, for reasons that I have explained elsewhere in this blog. Many people use the mean averagebut I think this is not, strictly speaking, sound statistical practice.We devised a Likert question to ask participants to rate the portable hole on five aspects: Size, Price, Reliability, Portability, and Durability. Overall, we can see that of the five qualities, Durability rated most highly, with an average weighted score of 4.

You can get started measuring success at your school, workplace, office, or organization by creating a free accountand making your own surveytoday. If you want that, I guess you can simple add all the averages up and divide by number of choices, but doing that seems mathematically suspect to me.