Comparing results in Excel

  • Page

    of 1

    Previous Next
  • UnblestCarpet 3 Apr 2012 11:06:04 232 posts
    Seen 10 months ago
    Registered 3 years ago
    Hey, I'm trying to compare some results in excel.
    I have a table which looks like
    Column Users, Data Column1, Data Column2, Data Column3 etc

    This all contains a set of results. I then have the same users and columns for another set of results which I want to compare to the original set of results.

    I'm confusing myself thinking about how I am going to do this. Ideally I'de have all the users on the same graph but it might get a bit messy that way. I know it would be very easy to do just one user at a time but it won't have as much impact that way.

    Does that make sense?

    Does anyone have any ideas about how to do this?
  • Chopsen 3 Apr 2012 11:07:31 15,996 posts
    Seen 5 hours ago
    Registered 9 years ago
    What are you trying to achieve? What's the point you're trying to make?
  • UnblestCarpet 3 Apr 2012 11:08:52 232 posts
    Seen 10 months ago
    Registered 3 years ago
    Maybe instead of having User 1 twice within the rows. I could just create two Data columns for each normal column. I.e. pre Data Column, Post Data column.

    That way the same user on the same row would have all their results together...
  • UnblestCarpet 3 Apr 2012 11:09:55 232 posts
    Seen 10 months ago
    Registered 3 years ago
    Chopsen wrote:
    What are you trying to achieve? What's the point you're trying to make?
    I've run a questionnaire asking some questions pre doing something. I then ran the same questionnaire post doing something. I want to compare results for the same people pre and post to show what difference there might be.
  • Chopsen 3 Apr 2012 11:13:42 15,996 posts
    Seen 5 hours ago
    Registered 9 years ago
    Are you trying to demonstrate how each question response varies post intervention, or how each user responds to the intervention?

    What I'm getting at is can you represent an individual response as a single figure (average of results or something for a given user)?

    Edited by Chopsen at 11:15:43 03-04-2012
  • UnblestCarpet 3 Apr 2012 11:16:38 232 posts
    Seen 10 months ago
    Registered 3 years ago
    I've used the Likert scale, so perhaps I could just look at the difference between pre and post results for a user and plot the result.

    i.e. Answer 1 Pre 5
    Answer 1 Post 4
    Difference being -1 as they've reduced their answer by 1.

    Sorry thinking aloud here.
  • UnblestCarpet 3 Apr 2012 11:18:03 232 posts
    Seen 10 months ago
    Registered 3 years ago
    Chopsen wrote:
    Are you trying to demonstrate how each question response varies post intervention, or how each user responds to the intervention?

    What I'm getting at is can you represent an individual response as a single figure (average of results or something for a given user)?
    How they respond post intervention.

    I gathered results pre intervention to get a baseline.
    Post intervention I want to see if the results have changed and if so by how much.

    I can give a bit more context if it might help?

    Edited by UnblestCarpet at 11:19:46 03-04-2012
  • mrharvest 3 Apr 2012 11:26:24 5,198 posts
    Seen 49 minutes ago
    Registered 11 years ago
    How many users and how many questions did you have? I'd probably just show average (or median depending on the distribution of the data) of initial value and change per question. But if your dataset is significantly small then there's probably nothing conclusive to show anyway.
  • UnblestCarpet 3 Apr 2012 11:39:52 232 posts
    Seen 10 months ago
    Registered 3 years ago
    I'm trying to show how a specific users perception changes post doing a task.

    My questions are relating to perceptions of mobile marketing methods. I gathered a baseline of what people thought pre performing a task and then post performing the task. I want to see if they've changed what score they attribute to the same question.

    For this part of my research my dataset is only 12 people (small I know but serves my purpose at this stage)
    I have about 8 questions which I am using at this stage, each of which have 3 categories. i.e.
    Question 1, what factor of usability would you attribute to marketing type....
    Category 1 (marketing method 1)
    Category 2 (marketing method 2)
    Category 3( marketing method 3)
    Each of which has a rating scale which can be considered 1 - 5

    * this obviously isn't the actual question I have used.

    I have done the calculation which I have previously mentioned, which seems to show what I am looking for.

    I'm basically trying to say. For marketing method 1, peoples perceptions increased or decreased post task.

    It would be good if I could extend that and perhaps do it on a group level . So maybe use average of my calculations to show a overall result.

    This is all meant to be fairly high level and not too statistical.
  • mrharvest 3 Apr 2012 11:55:31 5,198 posts
    Seen 49 minutes ago
    Registered 11 years ago
    So you want to show 12 sets of data which comprises of 3 by 8 questions each with a pre and post? That's a lot of stuff to show visually. I'd say you have to chunk it together somehow.

    I don't quite understand why you are interested in the individual users. Do they represent user groups (i.e. "male teenager", "tech savvy young adult", "housewife" etc.)?
  • UnblestCarpet 3 Apr 2012 13:02:59 232 posts
    Seen 10 months ago
    Registered 3 years ago
    mrharvest wrote:
    So you want to show 12 sets of data which comprises of 3 by 8 questions each with a pre and post? That's a lot of stuff to show visually. I'd say you have to chunk it together somehow.

    I don't quite understand why you are interested in the individual users. Do they represent user groups (i.e. "male teenager", "tech savvy young adult", "housewife" etc.)?
    Partially yes to your first paragraph. I intended to split the graphs by question, as it wouldn't be possible to represent all questions in the same graph and have it still be meaningful.

    I think grouping like you said might be best though. I could work out averages per question for my pre results. Then I could work out averages for my post result.

    I'm not actually interested in individuals atall. I just want to show the difference between pre responses and post responses.

    Sorry I know i'm confusing all of this as I am getting a little lost myself.
  • spamdangled 3 Apr 2012 13:16:54 27,355 posts
    Seen 1 day ago
    Registered 6 years ago
    Simplest solution - have a workbook with three pages in it.

    Create a grid, with rows being individual respondents and columns being responses to individual questions. Copy this grid on each page.

    Name page 1 "pre task" or whatever, page two "post-task", page three "difference".

    Fill in the data on the pre and post task sheets manually. On the "difference" page, put a formula in each cell that deducts the pre-task response from the post-task.

    So, for example, if Cell B2 refers to question 1, then the formula for the "difference" result would be "='post-task'!B2-'pre task'!B2". Drag this formula out to fill the whole grid.

    Then use the "difference" page to plot your results on the graph.

    Does that help?

    3DS: 4055-2781-2855 Xbox: spamdangled PSN: dark_morgan Wii U: Spamdangle Steam: spamdangled

  • UnblestCarpet 3 Apr 2012 13:24:45 232 posts
    Seen 10 months ago
    Registered 3 years ago
    darkmorgado wrote:
    Simplest solution - have a workbook with three pages in it.

    Create a grid, with rows being individual respondents and columns being responses to individual questions. Copy this grid on each page.

    Name page 1 "pre task" or whatever, page two "post-task", page three "difference".

    Fill in the data on the pre and post task sheets manually. On the "difference" page, put a formula in each cell that deducts the pre-task response from the post-task.

    So, for example, if Cell B2 refers to question 1, then the formula for the "difference" result would be "='post-task'!B2-'pre task'!B2". Drag this formula out to fill the whole grid.

    Then use the "difference" page to plot your results on the graph.

    Does that help?
    Thanks,
    That is exactly the approach I took a couple of posts back.

    I'm just having a go with this average method to see what that comes out with.
  • UnblestCarpet 3 Apr 2012 13:38:16 232 posts
    Seen 10 months ago
    Registered 3 years ago
    Ok, so the average graph has worked out really well to show the questionnaire results overall.

    The individual method as Darkmorgado mentioned seems to be a good way to drill down into specific questions.

    Thanks for the help
  • UnblestCarpet 3 Apr 2012 13:56:36 232 posts
    Seen 10 months ago
    Registered 3 years ago
    Sorry, last post about this un-less someone has a genius idea that I should try.

    I've now got a average response, pre and post plotted on a graph.
    I've also got the average difference, pre and post plotted (i.e. calculation of difference per response and then averaged)

    Both graphs seem to be what I was looking for and they support what I'm talking about.

    Thanks for the help!

    Edited by UnblestCarpet at 13:59:00 03-04-2012
  • HisDudness 3 Apr 2012 14:52:55 980 posts
    Seen 15 hours ago
    Registered 4 years ago
    In the future, I suggest you get that data in a machine readable format (e.g. flat file) and use pivot tables. They are the best feature of Excel and give you a lot of flexibility in making comparisons without a lot of fuss.
  • Page

    of 1

    Previous Next
Log in or register to reply