I have the following scales:

  • 1 (Strongly Disagree);
  • 2 (Disagree);
  • 3 (Not Applicable);
  • 4 (Agree);
  • 5 (Strongly Agree);

Questionnaire responses:

  • Question Item1: 2;
  • Question Item2: 3;
  • Question Item3: 1;
  • Question Item4: 1;
  • Question Item5: 4;

If I need to average the above responses into the Excel Sheet, while treating Question Item2: zero (since it is considered missing data), do I need to divide by 5 or 4 items to obtain the average?

$$\text{Average} = \frac{2 + \mathbf 0 + 1 + 1+ 4}{5}\quad\text{or}\quad\text{Average} = \frac{2 + \mathbf{\_} + 1 + 1+ 4}{4}$$

Kindly advise.


Dealing with missing data is a major topic unto itself, with many different available techniques and theoretical approaches. The two options you've mentioned are by no means the only options. However, of the two, the second is definitely better. It's equivalent to simply removing the missing observation before calculating the mean. The first option treats a missing observation as if the subject very strongly disagreed, which doesn't make much sense, in general.

  • $\begingroup$ >> I think the placement of the "N/A" in the middle of the scales make a difference as well, compared to placing it at the end or beginning of the scale. Participants may have viewed it as a "Neither Agree nor Disagree " item, in this case. Which formula would be appropriate in this scenario? Thanks $\endgroup$ – user39531 Aug 26 '16 at 15:50
  • $\begingroup$ @user39531 If you regard these responses as "neither agree nor disagree" rather than missing values, you should code them as 3 and use the first formula, as described in Student T's answer. $\endgroup$ – Kodiologist Aug 26 '16 at 15:56

While @Kodiologist is correct, your data doesn't look like missing data to me. Not Applicable means the respondent is nether agreeing nor disagreeing the question. It's NOT a missing value because it gives you something about the respondent. In this case, you should divide by 5.

EDIT: It doesn't make sense to use 0 for Not Applicable because it's not a missing value. What about 3?

  • $\begingroup$ OP's description certainly suggests that, but in that case, he or she should presumably use 3 for the item's score rather than 0. $\endgroup$ – Kodiologist Aug 26 '16 at 4:08
  • $\begingroup$ @Kodiologist I agree. I'll edit my answer. $\endgroup$ – SmallChess Aug 26 '16 at 4:10
  • $\begingroup$ If you advise to use "3" for N/A, then are you saying that I should use the first formula, and divide by 5 to obtain the average value. Please confirm $\endgroup$ – user39531 Aug 26 '16 at 14:31
  • $\begingroup$ @user39531 yes that would be my suggestion $\endgroup$ – SmallChess Aug 26 '16 at 14:40
  • $\begingroup$ Appreciate your input, I tend to agree with Tim's explanation below though. Unless, the point your are making is coming from a tested theory that statisticians have adopted. Many thanks $\endgroup$ – user39531 Aug 26 '16 at 14:45

"Not applicable" is not a missing data but a valid information! Imagine that in survey you ask about ages of respondent's children:

Is your son (1) much younger, (2) younger, (3) same age, (4) older, (5) much older then your daughter, or either is the question (6) not applicable because you do not have either son, daughter, or both?

If you coded "not applicable" as $0$, you'd conclude that people who do not have sons, or do not have daughters have much much younger sons than daughters. If you code it as $7$, then you'd conclude that they have much older sons. If you code it as some middle value, you'd conclude that childless person's children are in the same age...

If you need to calculate average, then exclude those participants who marked "non applicable" from this calculation (you can report count or percentage of such cases) and treat "not applicable" as another dummy variable ignoring the fact that it was a part of the same question.

  • $\begingroup$ According to your theory, I see you advise me to utilize the second formula, by eliminating N/A response, and divide by 4. I am still not sure which is the better option. $\endgroup$ – user39531 Aug 26 '16 at 14:33
  • $\begingroup$ @user39531 Yes, you should use the second one since "not applicable" is not part of the Likert scale in your question. $\endgroup$ – Tim Aug 26 '16 at 14:35
  • $\begingroup$ I see your point. If I use 3 for N/A, then it will lean more towards the agree direction than disagree, given that, 3>2>1, it will inflate the average value. When in fact N/A means that the participant does not agree nor disagree. That is the reason I should pick the second formula. Thanks for the clarification. $\endgroup$ – user39531 Aug 26 '16 at 14:41
  • $\begingroup$ @user39531 I wouldn't say it meant that he "neither agrees, nor disagrees". Imagine that the question is about liking pink color and you asked blind person about it. Obviously, blind person would say the question is not applicable. On another hand, someone else, who is not blind, may tell you that he neither lines it, nor dislikes it. Those are the two different answers that cannot be treated equally. $\endgroup$ – Tim Aug 26 '16 at 14:47
  • $\begingroup$ sure, we need to make a distinction between "neutral" and "not applicable." But in any case, I believe that the Average of second formula is more sensible to consider. Thus not counting the responses received for N/A. Thanks $\endgroup$ – user39531 Aug 26 '16 at 14:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.