Question on tabulating data

[joynerCN]

I think that's the word for it anyway.

Anyway, I have a data sheet. In each row are students' responses to 15 one-word free-response questions.

I want to tabulate how frequently each response appears within each column. So if, for example, it was just two comma-separated columns:

Student 1, Cheese
Student 2, Spinach
Student 3, Spinach
Student 4, Pizza
Student 5, Cheese

I would want something at the bottom to say:
Cheese, 40%
Spinach, 40%
Pizza, 20%

But since the responses are free-response, I can't just add the normal =COUNTIF for each option.

So, how do I go about doing this?

[MarvinP]

Hi

I'd do this with a pivot table for each column of answers. See the attached. I didn't need any formulas to do this, simply create some pivot tables and drag and drop column headings. Show the second as percent of column total.

[joynerCN]

Oh, great! That's easy. Just one question: do those update automatically, or do you have to recreate it if the data changes?

[MarvinP]

Hi,

I'm thinking you want to create a Dynamic Named Range for the Pivot Table Data. That way you use the Named Range in your Pivot Data Set and it will update. You may also need to press the Pivot Table Refresh to insure they are updated after changing some numbers.

See http://www.ozgrid.com/Excel/DynamicRanges.htm or
http://www.beyondtechnology.com/geeks007.shtml for DNR.