Hello all,
My first post.
I wish I knew enough to properly phrase my question - it would no doubt have yielded better search results!
I have been following this forum periodically, and it has always provided me with invaluable assistance - but I have decided to take the plunge and pop up this question, since if I can be helped - it will save many hours of work each year!
I work at a University Faculty, and offer support to students.
I first need to identify those who are struggling in particular subjects, and then begin assisting them.
What I do currently:
I have 5 different Excel spreadsheets, obtained from various sources - with the test results of students in those 4 particular subjects.
I then select all those who obtain >45% in each of the 4 subjects, and start a new spreadsheet with 4 columns, listing the STUDENT NUMBER of the students selected (>45%), in those 4 subjects.
For example:
A [PolSci11] B [PolSci12] C [PolSci13] D [PolSci14]
A1 [Stud #] B1 [Stud #] C1 [Stud #] D1 [Stud #]
A2 [Stud #] B2 [Stud #] C2 [Stud #] D2 [Stud #]
A3 [Stud #] B3 [Stud #] C3 [Stud #] D3 [Stud #]
A4 [Stud #] B4 [Stud #] C4 [Stud #] D4 [Stud #]
As is hopefully clear, each of the columns list ONE occurrence of a unique Student Number in that specific column, for each of the subjects.
Furthermore, a unique Stud # might only appear in 1 column (if that student only achieved >45% in one of the 4 subjects) - OR, the same unique Stud # might appear (obviously at different places) in ALL four columns (where a student achieved >45% in all 4 courses).
I know how to use Countif in order to tally how many times Value X (e.g. Student Number "[12656607]") appears in Range A1:D4.
But what I'm wondering is - would it be possible to design a Formula / arrange the data, so as to not only show how many times [12656607] appears in total, but for which subjects?
Is this a Pivot Table solution?
In short - some subjects are more important than others. A student who is >45 in PolSci11 might be worse off than a student performing poorly in both Polsci12 & Polsci14, since the former carries much more credits, or vice versa.
So I need to not only know the frequency that a particular student/student # crops up in the range, but I need to know "where" as well (i.e. for which subjects)...
Any conceptual suggestions would be greatly appreciated. I have no clue whether I need to start learning Pivot Table functionality to achieve the above, or whether it would be possible to design a formula that would do the same?
Bookmarks