![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png){kind=small}
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).
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).
![[identity profile]](https://www.dreamwidth.org/img/silk/identity/openid.png){kind=small}
From:
no subject
From:
no subject
Thanks, though.
From:
no subject
From:
no subject
From:
no subject
If you have any ability to use mySQL, it will work a lot more easily and you can input data with XML if the data can come to you that way. The problem with this is that not all data comes in XML, and the hard part is getting the data into mySQL in the first place, short of typing it all in. What sort of formats can you get the data in?
From:
Thanks for the encouragement!