2

My research question is whether aptitude tests are a good indicator on how students will perform on course exams. I have already performed an analysis on total exam score compared with total aptitude score.

I am now wondering whether getting a certain question on the test right or wrong is related to how that student scored on the aptitude test. I have heard that Spearman's Coefficient is the best bet for this, but I'm unsure that's the best methodology.

I'm doing this project in excel and working with 100 columns. Column 1 is student ID, Column 2 is aptitude test score, and 3-100 are individual test question responses (either 1 or 0).

What do you recommend?

Adam
  • 21

2 Answers2

1

If you had continuous data, the go-to test would be the two-sample t-test, which can be expressed as a regression on a binary variable $V$.

$$y = \beta_0 +\beta_1V+\epsilon$$

For ordinal $y$, like you have, there is ordinal regression; one software implementation is orm in the rms package in R. The special case of the ordinal regression implemented in orm where there is just a binary predictor (as above) is equivalent to the Wilcoxon Mann-Whitney U test.

Consequently, I would suggest a Wilcoxon Mann-Whitney U test.

Dave
  • 62,186
  • 1
    Most likely the regression would be done in the opposite direction, addressing the question of how likely students are to get a question correct conditional on their aptitude test score. Moreover, this seems to call for estimation rather than formal null hypothesis testing. And, given there is a relatively large number of questions, exploration and visualization would seem to be indicated before anything else. – whuber Jun 06 '22 at 12:11
0

Yes, as both you and @Michael M suggest, spearman's rank correlation would be a good choice. The first link below details how spearman's rank correlation may be implemented in excel. Another option would be the biserial correlation which can be used when one of the variables is categorical (e.g., dichotomous like columns 3 - 100) and the other is continuous (like your aptitude test scores). The second link below details how the biserial correlation may be calculated using excel.

https://www.ablebits.com/office-addins-blog/spearman-rank-correlation-excel/

https://www.real-statistics.com/correlation/biserial-correlation/

Preston Botter
  • 2,611