How Can One Tabulate Multiple Choice Questions in a Database?

[auroral3orealis]

I need to learn how to create a database on Excel as we have given a diagnostic test to our students, and I need to create a database that allows me to create various sorts of table and figures. I noticed, however, that if I enter a, b, c, d as their the students' answers to multiple choice questions and then select their answers and click on insert figure, Excel fails to read the data, and I end up staring at a blank page. I have no idea how to fix that! I will be more than grateful if someone could give me some advice on how to do this.
I really need to fix this and finish the database.

Thanks in advance!

 

[pbornemeier]

Please explain in more detail. Ii is inclear what you want to accomplish.
Welcome to the Board!

What version of Excel are you using?
Will the students be using Excel to take a test?
Will you be transcribing their answers into an Excel worksheet?
Insert figure??
Are you creating a test from a bank of questions?

Be detailed.

 

[auroral3orealis]

Thank you for your reply!

To answer your questions,
1- I am using Office 2007.
2- The students have already taken the test. I just need to create a database that saves their answers. We would like to be able to categorise them according to their gender, nationality, first language and age, and that's why we need the database to be able to tabulate the selected data. So yes, we be transcribing their answers into an Excel worksheet and will need to insert figures and tables. They have answered multiple choice questions, so I tried to enter a, b, c, d as their answer on an Excel Worksheet. Then I selected a bunch of students' answers, and selected insert figure. But it doesn't work that way I can upload the worksheet I have made if my description is not detailed enough.

Thanks in advance.

 

[pbornemeier]

Are the students answers just single letters ?
What figures and tables do you have to insert? Where are they coming from?
What are you using "Insert Figure" for? How are you using it?

 

[auroral3orealis]

Yes, the answers are only single letters as they stand for the choices of each question.

I would like to tabulate them according to their nationality, age, gender, first language, etc. I think it is best that we use bar or pie charts mostly and sometimes tables.

And yes, what I do is that I select the column (for only one question) that I want and then click on insert and then select bar chart and this is the result:

I know I am plainly doing something wrong, but a friend suggested I should use SPSS and not Excel. I am a tyro at this, but I think there should be a way to work this out in Excel.

 

[pbornemeier]

The values need to be numeric to be pulled into a graph.
If put these formulas in the indicated cells then drag them to the right:
G8 =COUNTIF(G$3:G$6,"A")
G9 =COUNTIF(G$3:G$6,"B")
G10 =COUNTIF(G$3:G$6,"C")
G11 =COUNTIF(G$3:G$6,"D")

you will get a count of the number of times each letter appears in each column which can then be graphed.

 

[mgirvin]

In order to get height in a column in a chart, you need a count of each type of items. You could do this with the COUNTIF function.

If you had data like this:

Excel Workbook
	A	B	C	D	E	F
1	Gender	Age Group	7	8	9	10
2	F	A	a	c	c	c
3	F	A	a	c	c	d
4	F	B	a	c	c	c
5	F	A	b	c	b	c
...

Create tables like these, then highlight table and use keyboard for default chart ==> Alt + F1, (or insert ribbon tab, column chart):

Excel Workbook
	H	I
1	Gender	Count Gender
2	F	4
3	M	0
...

Cell Formulas
Range	Formula
I1	="Count "&H1
I2	=COUNTIF($A$2:$A$5,H2)
I3	=COUNTIF($A$2:$A$5,H3)

Excel Workbook
	J	K
1	Age Group	Count Age Group
2	A	3
3	B	1
4	C	0
...

Cell Formulas
Range	Formula
K1	="Count "&J1
K2	=COUNTIF($B$2:$B$5,J2)
K3	=COUNTIF($B$2:$B$5,J3)
K4	=COUNTIF($B$2:$B$5,J4)

Excel Workbook
	L	M
1	7	Count 7
2	a	3
3	b	1
4	c	0
5	d	0
...

Cell Formulas
Range	Formula
M1	="Count "&L1
M2	=COUNTIF($C$2:$C$5,L2)
M3	=COUNTIF($C$2:$C$5,L3)
M4	=COUNTIF($C$2:$C$5,L4)
M5	=COUNTIF($C$2:$C$5,L5)

Excel Workbook
	N	O
1	8	Count 8
2	a	0
3	b	0
4	c	4
5	d	0
...

Cell Formulas
Range	Formula
O1	="Count "&N1
O2	=COUNTIF($D$2:$D$5,N2)
O3	=COUNTIF($D$2:$D$5,N3)
O4	=COUNTIF($D$2:$D$5,N4)
O5	=COUNTIF($D$2:$D$5,N5)

Excel Workbook
	P	Q
1	9	Count 9
2	a	0
3	b	1
4	c	3
5	d	0
...

Cell Formulas
Range	Formula
Q1	="Count "&P1
Q2	=COUNTIF($E$2:$E$5,P2)
Q3	=COUNTIF($E$2:$E$5,P3)
Q4	=COUNTIF($E$2:$E$5,P4)
Q5	=COUNTIF($E$2:$E$5,P5)

Excel Workbook
	R	S
1	10	Count 10
2	a	0
3	b	0
4	c	3
5	d	1
...

Cell Formulas
Range	Formula
S1	="Count "&R1
S2	=COUNTIF($F$2:$F$5,R2)
S3	=COUNTIF($F$2:$F$5,R3)
S4	=COUNTIF($F$2:$F$5,R4)
S5	=COUNTIF($F$2:$F$5,R5)

 

[auroral3orealis]

Thank you so much for your reply!
Forgive me if this sounds stupid--like I said, I am a neophyte at this--but do I add two of each column? I mean, do I add column counts on the right side of the data, creating, for example, a gender column again with a gender count next to it?

 

[mgirvin]

do I add column counts on the right side of the data, creating, for example, a gender column again with a gender count next to it?

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Yes.<o></o>
<o></o>
For each column you need a chart for, you must create a unique list of items in the column (for example in the Gender column the possibilities are F, and M, so the unique list is F, M) and then to the right use the COUNTIF function. When you create the COUNTIF function, be sure that when you enter the range argument (cells with all the data) that you put the dollar signs in to lock the range. It is easy to put the dollar signs in - simply hit the F4 key immediately after highlighting the range - and the F4 will put the dollar signs in for you. The criteria argument should then just be the cell to the left of the cell that houses the formula and do not put dollar signs for that one. After you copy it down, the table is complete and you can make a chart from that. You will have to repeat that for each column that requires a chart.<o></o>

 

[mgirvin]

You could also create a PivotTable:

1) With one cell selected in the data set, go to the Insert Ribbon tab, then in the Tables group, click on the PivotTables button, then to put the PivotTable on a new sheet hit Enter. (Or just use the keyboard Alt + N + V + T + Enter.)
2) On the right in the PivotTable Field List, drag the Gender field (top of PivotTable Field List) to the Row labels area (rectangle box at bottom of PivotTable Field List).
3) On the right in the PivotTable Field List, drag the Gender field (top of PivotTable Field List) to the Values area (rectangle box at bottom of PivotTable Field List).
4) Then make a Chart from the table.
5) You could repeat the process for each field that you want a table for.

If the data changes, you will have to right-click in the PivotTable and point to "Refresh" to get the PivotTable to show the new data. In the last post, with the formulas, you do not have to "Refresh" becasue formulas do that automatically.