creating a table with multiple columns

[bart230]

for a High School science project my daughter created a survey with 39
possible responses (3 groups of 13 responses in each group) the people
taking the survey are supposed to pick the top 4 responses in each group
(numbered 1-4, 1 being the highest). Other information is Age, grade and
gender of the person taking the survey. What would be the best way to create
the table and eventually charts to diplay the results of the survey? I was
thinking of a flat table with a number in the cells correspondig to their
answers but I have no idea how to create the charts or compare the answers
with others to find patterns. So far she has 400 surveys to compare. Any
suggestions?

 

[Bob Phillips]

This looks best handled by a pivot table, but without a bit more detail, it
is a tad difficult to suggest where you begin.

Can you give us some idea of the questions, say the 3 groups, and 3 or 4
questions in each group, and we can give a better steer.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[bart230]

Thank you for helping the survey is below. Picture it in three columns. Also
she would like to be able to analyze the data somehow to show relationships.
Like if the chose a fear of blood dig they also show a fear of needles?


What Teens Fear Survey


This is a voluntary and anonymous survey on what, as the name says, teens
fear. All I need is your age, grade, and gender. Pick 4 fears you may have
from each of the 3 categories: specific, social and agoraphobia. Number them
in order from greatest to least. 1 being the most feared and 4 being the
least. If you don’t have a fear in a category, mark “noneâ€. Or, if you have
your own you would like to add, write it in “otherâ€. Only mark fears you
have. Please write on this paper. When you are done, give it back to your
teacher. Please fill out only one survey.

Age

Grade

M/F


Specific phobia # Social phobia # Agoraphobia #
Certain insects Public speaking Being away from home
Certain animals Meeting new people Being in or driving a car
Certain day, date, or number Being center of attention Going through a
tunnel
Heights Being observed doing something Being in a crowded area
Thunder/Lightning Being teased or criticized Being on a bridge
Confined spaces Eating in public Being alone
Dark Drinking in public Losing control in public
Blood Dating Fear of dying
Needles/Shots Talking to people in authority Fear of going crazy
Knives/Sharp objects Using public bathrooms Being in a new place
Fire Looking other in the eye Fear of fainting
None None None
Other Other Other

 

[Del Cotter]

Thank you for helping the survey is below. Picture it in three columns. Also
she would like to be able to analyze the data somehow to show relationships.
Like if the chose a fear of blood dig they also show a fear of needles?

I would suggest a table arranged in the following eight columns:

Survey response number
Age
Grade
Sex
Rank # of fear (1-4)
Specific phobia
Social phobia
Agoraphobia

Each survey response will take up four rows, and the response number,
age, grade and sex will be redundantly repeated in those four rows. This
would be a good place to use a relational database, to avoid
duplication, and Excel can do relational databases, but if you just want
to do this as a flat file and put up with the redundancy, that's okay
too.

You can now use pivot tables to show breakdowns and subsets, and pivot
charts for some basic presentations. More complicated presentations may
be possible if you use e.g. panel charts as in the examples here:

http://peltiertech.com/Excel/Charts/StackedCharts.html

These chart types may use pivot tables as the base, but I don't think
you'll be easily able to design them using Pivot Charts.

PS an alternative table design using only seven columns would be this:

Survey response number
Age
Grade
Sex
Rank # of fear (1-4)
Phobia type (Specific, Social, Agoraphobia)
Phobia

This time a single survey response would be twelve rows instead of four.
But while this would be good database practice, I again don't think you
need to be too bothered with it. It would probably make your reports
harder to design unless you're used to that way of thinking.