How do I automatically rearrange data located in Microsoft Excel?

October 27, 2013
Pinterest Stumbleupon Whatsapp

I’ve got online responses for my survey in Excel. In the questionnaire, each question has 3 choices of answers (yes=3, no=1, ?=2).

Besides, half of the questions are reversed (yes=1, no=3, ?=2). I need to code answers into points in order to count points for each person in. How do I do that?

Please, can you provide me a detailed explanation? And also afterwards how do I summarize the points for each person and how do I count the average for males and females separately?

Ads by Google

  1. Bud I
    October 28, 2013 at 2:34 pm

    I would need more details to give a complete answer. Do you have a different sheet for each responder, or are all the responses on one big sheet? Is the gender in a given cell for each response? Are the reversed questions in a fixed format (yes=1, no=3, ?=2), i.e., exact same spacing? Are you looking for a macro or a function statement? (The latter could be done, but it would be messy.)

    In general terms, you can use a Find on "yes=1, no=3, ?=2". if the result is a number, x, then REPLACE the incorrect 11 characters with the correct 11 characters starting at character 'x'. You will also have to change the '1' and '3' responses.

    If all the responses are in the same column, a simple SUM function should do the trick. Otherwise you will have to do another search of some sort.

    Assuming all the responses are in the same column, an AVERAGEIF function will do the job of averaging by sex.