Figures Analysis

  • Thread starter Thread starter Sisilla
  • Start date Start date
S

Sisilla

Hi,

I am a student worker, and the professor I work for has
given me an excel assignment that I hope someone on this
newsgroup can help me with. He has given me a spreadsheet
for each of his students. The spreadsheet shows questions
versus attempts. Attempts are given 0 if the question was
answered wrongly, and 1 if the question was answered
correctly. (See below)

1 2 3 4
A101A 1
A101B
A102A 0 0 1
A102B 0 0 1
A103A 1
A103B 0 0 0
A104A 1
A104B 0 0 0
A105A 0 0 1
A105B 0
A106A 0 0
A106B
A107A 0 0
A107B 0 1
A108A 0 1
A108B 0 0 0
A109A 0 0 0 0
A109B 1
A110A 0 0
A110B

By looking at the spreadsheet, it is easy to tell how many
attempts it took before the student got a question right.
My boss wants me to think of a way to analyze the
spreadsheet to get an idea of how each student is
progressing, preferably using a graph of some sort.

I have been unable to come up with something thus far. I
appreaciate any help.

Thanks,
Sisilla
 
Hi Sisilla

Why don't you start off by counting the number of attempts for each
question - use the Count function

Then calculate whether the question was answered successfully - use the sum
function to add up the attempts - must be 0 or 1

Then do cumulative totals of the attempts and the successes - ie add the
attempts for one particular question to the total attempts for all the
previous questions.

Divide cumulative successes by cumulative attempts and express as a %age.

This gives you a column of %ages that you can graph to show progress - you
can see whether the student is taking more attempts per success or vice
versa because the cumulative figures will trend up or down.

You could do something similar to show up questions not answered (count of
attempts =0)

Hope this gets you started. It's actually quicker to do than to describe.

Geoff
 
Back
Top