April 1, 2012 at 3:26 pm
I wish to get a formula which will enable me calculate the average of a row. I am working on student performances which are listed in a row. At the end of the row I wish to include the Total and the Average but am failing to get a formula calculating the average. Please help.
Joses#
April 1, 2012 at 8:12 pm
Look up computed columns in Books Online.
April 2, 2012 at 5:07 am
A couple of points....
What sort of average:
Mean: Sum of results / number of results
Median: the middle of the results set when ordered by vale (mean of number either side of middle if an even results set)
Modal: the most common result (you may want these to be in historgram groups)
I am presuming that you want mean, but your challenge is that you want to ignore results of zero because that indicates that the student did not take the test.
Having results on a single row smacks of bad table design; what happens if a new test is introduced, you now have to modify the table design and all your queries. You would be better off in the long run normalising the tables.
Student: Student_ID, student details (name, address, dob etc..)
Test: Test_ID, Test details (Date, course(ID), Pass mark, etc....)
Result: Result_ID, Student_ID, Test_ID, Score.
This makes it easier to get: All the results for a test, all the results for a student, all the passed/failed results, ratios of students passing/failing, ranking tests by difficulty of pass rate etc...
April 2, 2012 at 9:29 am
Thanks for your response. You are right, I wat to calculate the mean leaving out those subjects which the student has not attempted. Let me try to apply your suggestion.
Joses
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply