Making sense of raw data using Excel?
I've conducted a survey and exported the result. The exported data isn't that helpful. Different questions of the Survey form the first row of the excel sheet. Under every question the answers by various participants are listed. The answers are mostly Yes/No/Can't Say.
Is there any way to find percentage of answers for each question. For eg for Question 1 how many answered in Yes and how many answered in No. Would Pivot tables help?
The Sheet would look as shown
Q1 Q2 Q3
Yes No No
No Yes No
Can't Say No No
No Yes Can't Say
- Anonymous1 decade agoFavourite answer
at the end of you r columns, you should have sum totals and then in the cells under that, have your formula calculate the percentage or what ever else you wish to know.
- kobayashiLv 44 years ago
What format is the information which you get from the gadget? Is it delimited or fixed width; without a gaggle of extraneous archives in the mixture? no count if it is, then it is copied into Excel and separated into columns making use of the textual content cloth to Columns function. Then formulation could be utilized to the information to get the outcomes which you're in seek of. it is even available that Pivot Tables would desire to furnish the answer on your difficulty. VBA is probably not mandatory. If the calculations are what you describe above, the toughest area of the pastime may well be to get the information into Excel. i assume that your kit has the flexibility to furnish you the information as a textual content cloth rfile of a few sort. With a pattern of the uncooked archives and the spreadsheet which you're making use of, i does not techniques attempting to clean up your difficulty. The formulation used (in undeniable language sort) may well be mandatory as i'm not a scientific individual. deliver me a message in case you have an pastime.
- Anonymous1 decade ago
IMO, the easiest way to do this is with the "COUNTIF" function.
The format for "COUNTIF" is as follows:
For your case, for each column of question responses, you will want to make three different COUNTIF's in three cells. One with the criteria being "Yes", one being "Can't say" and the final one being "No". Note, when the criteria is text like you are doing, you need to put the criteria in quotation marks.
For example - @COUNTIF(B5:b50,"Yes")
This would search the cells b5 through b50 and count the number of yes responses.
It's pretty easy, but just in case, I made a demonstration file that you can download to take a look at.
After that, you can plot, do statistics, percentages, whatever data analysis you might want to do.Source(s): Me...years of working in Excel
- Anonymous1 decade ago
this is how I would do it
first add a column before the data
at bottom of the data label the three row 'yes, no, cant say
then in each column beneath in order use the countif statement to count the number of yes in the column next row would be countif no so on
this will give you the total number of times each one appears in the column
totals q1 q2 q3
then the percentages would be for yes =yes/sum formated for percentage
that would help
- What do you think of the answers? You can sign in to give your opinion on the answer.
- anonimousLv 61 decade ago
I am most comfortable with older versions of excel. I would use the subtotals feature under the data pull down menu. I assume that each question has it's own column and that each column would be sorted independently to get similar answers next to each other for each question. using the counting option to summarize each column would then give you a count for each type of answer.