Hello,
I am currently working on analyzing a data set with the following column headings:
1. Unique ID (each row is a single student)
2. Grade
3. English language proficiency
4. Ethnicity
5. Special Ed Status
6. Testlet Order
7. Fall MAP Score
8. Winter CBMR- WRC
9 Winter CBMR Accuracy
10. Item Response Time (item 1)
11. Item Accuracy (item 1)
12. Testlet Name (for item 1)
13. Item Response Time (item 2)
14. Item Accuracy (item 2)
15. Testlet Name (for item 2)
....this sequence repeats for a total of 93 items.
I need to calculate the average accuracy that each student received on each testlet. There are 3 testlets: Word, Non-Word, and Pseudohomophone. All students completed all 3 testlets. However, the number of items that each student completed in that testlet varies by student (the data is in wide format).
I also need to calculate the average time spent only on items that were answered correctly, by testlet, by student.
I have been using variations of AVERAGEIF(), but this requires completing cells individually, and it is a rather large data set. I am unable to drag and re-use functions because each student has a varying number of items per testlet.
I would greatly appreciate any thoughts or suggestions. Thank you in advance. I've attached the excel sheet that I am working with. I have started to do several of these calculations, using formulas, but individually, by hand.
Bookmarks