Calculate stats from multiple csv files

I work for a University in Michigan. We are starting to use a software title to deliver and administer tests/surveys. After the test is done the program will write 2 types of files; 1 is an html of all the computer names and their total score, the other is an individual .csv file which details Question number, Question Title, Total Points, and Points Scored. Is there a way to automate the creation of a document that uses all the .csv files to create stats?

Example:
(Suppose that each person has their own .csv file with this info)
Person 1
Question 1: 0 points
Question 2: 1 point

Person 2
Question 1: 1 point
Question 2: 1 point

I am looking for a way to output that 50% of the people got Question 1 right, and 100% of the people got Question 2 right. Assuming that each question will be worth 1 point.

Attached are 2 of the .csv files that the program spits out when the test is completed. Also please ignore that the first question was worth 4 points, in the future it should 1 point or 0 points.

Please let me know if what I am asking is possible.

Thanks
~Fletch

Anyone? This seems like this would be easy for a avid Excel user

I have no clue about excel alone, but here is my idea:
Use the copy command.
Get all of your relevant CSV's in one directory, open a command prompt in that dir and type
"copy *.csv combo.csv"
The result will be a concatenated file of all your CSV's.
For more options see this page: http://www.lagmonster.org/docs/DOS7/z-copy.html
Then it is up to excel to process them.

Originally Posted by martix

I have no clue about excel alone, but here is my idea:
Use the copy command.
Get all of your relevant CSV's in one directory, open a command prompt in that dir and type
"copy *.csv combo.csv"
The result will be a concatenated file of all your CSV's.
For more options see this page: http://www.lagmonster.org/docs/DOS7/z-copy.html
Then it is up to excel to process them.

Originally Posted by shg

I think martix makes a great suggestion. You can open the combo file in Excel and use a few formulas to summarize the results.

It may be possible, but I'm trying to figure out a way that would be easy to explain to someone so that they can do it. With this option I would have to copy the files into one file (as stated with your suggestions) then open the master one in Excel but would then have to manually select all the points scored for question 1 and tell them to add together then do the same for question 2 and so on. This would be very tedious (and thus not what I'm looking for) when there is 20+ questions and 30+ result files...

Your suggestion would work much better if it put all of the scores horizontally instead the copy command joins all the .csv files vertically.

Originally Posted by John_Franco

Hi Fletch,

I attach the solution for you.

The file is ready to receive your data, just open each csv file in excel and separate it into columns with Text to Columns Command, then copy and paste it in B2.

Copy and paste formulas down until your last record

The process in summary:

You can do it with the COUNTIF function.

Count the number of Questions 1 with Scored 0 and divide by the number of questions 1... and this way with each question.

Each question has two conditions:
Question 1: Scored 0 = 10 and Question 1: Scored 1 = 11
Question 2: Scored 0 = 20 and Question 1: Scored 1 = 21

Use concatenate function to turn those two conditions into one: 10, 11, 20, 21 ...

Done

I hope this help you

You can know more about COUNTIF function and examples in: http://www.excel-spreadsheet-authors.com/countif.html

Wow, thanks I will give it a try, but I am out of the office now, but I will report back on Monday how it worked!

Thanks again!