Steve (akicif) wrote,

  • Mood:

Quiz analysis - Excel?

Does anyone know anything about using Excel for qualitative (as opposed to numeric) analysis, please?

I'm looking at designing a quiz or two, but would like to be able to analyse the results in slightly more depth than Excel seems to want to let me.

Say I have two columns of responses, and each column has about four possibilities. It's easy enough to do a chart for each column showing the numbers for each response "manually", by using =COUNTIF(A1:A25,"clicky thing!") or similar and sticking these in a column of their own - or I can be a little more clever and have the choices in a column of their own (B1 has "Clicky Thing!", B2 has "Shiny!", B3 has "Look! the Winged Victory of Samothrace") and have =COUNTIF(A$1:A$25,B1) and so on. [Side thing: if there was a function that would take all the values in A and return just the four unique values and put them in B, I could save myself even more typing]

But anyway, I can do that stuff more or less manually, and it doesn't return much more than the charts from the LJ polls.

What I'd like to do is be able to automagically compare two sets of answers, so if I had a second question (D contains the answers, E contains "Impeccable manners", "Excellent taste" and "His sombrero and flippers", with the matching frequency counts in F), I could chart either all the answers to the first question given by respondents who consider the true characteristic of a gentleman to be his elegant dress sense, or similarly chart all answers to the second question made by magpie-types.

It's all doable slowly and manually with lots of sorting of columns and chopping bits out here and there, but the time taken seems to go up rather quickly with the number of questions, and while I have Grandiose Plans, I'm also Dead Lazy.

Oh, and I know I said this was qualitative, but are there any numerical measures of correlation that I should consider applying here?

Previously suggested solutions have involved nesting or concatenating COUNTIFs (doesn't seem to work), using SUMIF (only works for numbers), or doing the whole thing with mySQL instead (I'm not sure I know enough to do that, but it would have the advantage that I could -in theory- also do all the graphing with the GD package in PHP (which I've been meaning to learn for a while now).

  • My tweets

    Mon, 19:57: Not overly hoppy. Slightly wheatie. Goes well with Spotify link to remistressed album. Tasty indeed - Drinking a Pale Countess-…

  • My tweets

    Sat, 20:43: Still don't like sour beers officially, or cherries. But once again, this is a beautiful exception. - Drinking a Never Mind the…

  • My tweets

    Fri, 14:03: Note the date. Weaponised transphobia is a lot more recent than they'd have you think!

  • Post a new comment


    default userpic

    Your reply will be screened

    Your IP address will be recorded 

    When you submit the form an invisible reCAPTCHA check will be performed.
    You must follow the Privacy Policy and Google Terms of use.