User and Reference Manual

Student Scores and Statistical Analysis Spreadsheet

For use with OpenOffice.org Calc

Version 1.0

© Carlo Andreacchio and Sharleen Kernick, 2003

Contents

• Chapter 1: User Manual
  • 1.1 How to Use the Application
  • 1.2 Expected Values
  • 1.3 Assumptions and Limitations
  • 1.4 Analysis Interpretation
• Chapter 2: Reference Manual
  • 2.1 Array Names and Ranges
  • 2.2 Preparing Raw Data for Analysis
  • 2.3 Submission Sheet
  • 2.4 Analysis- Sex, Residency, Degree Programme
  • 2.5 Returning Statistical Values
  • 2.6 Determining the Spread of Students

Chapter 1: User Manual

1.1 How to Use the Application

To use the Student Scores sheet, open a raw data file containing comma delimited text in the format .csv in OpenOffice.org, select all of the contents and paste it into the Student Scores Raw Data sheet from cell A1. The application relies on cell A1 containing the course name with the actual student data starting in cell A2. Once the data has been entered in the correct way the application will process the data for the information to be entered in submit and analysis. Submit contains the information that is required by the administration for processing the students while analysis contains various analyses of the students as a group.

Contents

1.2 Expected Values

In the raw data sheet, column A requires the student number, unique to each individual student. Every student must have a student number. Column B contains the title, such as MR, MS, MRS, MISS etc. C contains the first initial of the student while D contains their surname which must be all in upper case. A four digit number representing the degree program must be entered in column F. Column G contains the enrolment status. If this entry is blank for a student it means they are currently enrolled. The mark for the practical assessment and the exam are entered in columns H and I and must be a mark out of 100. Column J contains the students gender with M representing male and F representing female. In column K the students enrolment type is represented as a 1 for full time or a 2 for part time and in column L, F represents international and N local.

Contents

1.3 Assumptions and Limitations

There are a few limitations to the student scores sheet. One major limitation is that the sheet can only have a maximum of 500 students. The data must be entered starting from cell A1 in the Raw Data sheet. The sheet is designed for data to be entered with a unit heading and the actual student data to start in A2. The sheet still will work if there is no unit name because it relies on the students' data being inserted from row 2 downwards. Every student must have a student number to have their results counted. If there is a blank in one or both of the scores columns and in the enrolment status column the mark or marks that are missing are assumed to be 0. The weighting for the overall mark must be 40% from the practical mark and 60% from the exam. There are different unit codes and these refer to an individual degree. The codes for the degrees must match the ones given in the table. If there is a degree that doesn't fit into the specified codes it is listed under other.
The grades must be assigned using the values given in the table:

Table 1: Grades that are awarded and the corresponding percentages.

Contents

1.4 Analysis Interpretation

Table 2: Course Codes, Abbreviations and Courses.

Figure 1, which is a screen shot from the analysis page, provides information on how different groups of students performed in the unit. Mean provides the average score achieved in the unit by the stated group of students. No. refers to the number of students from the stated group that did the unit, while the heading Fail gives the number of students in the stated group that failed. The groups consist of the different types of degrees as stated previously. The heading Others refers to all the other degrees that do not fall under an established course. Next the male and female pass rates are looked at, as well as the local and international students. As a means of comparing these a pass rate is worked out and specified as a percentage and listed under the heading Pass Rate. This is very useful for anyone who wants to compare the two genders or local and international students.

Figure 2 provides the statistics for the practical, examination and final marks, under headings with the corresponding names. Such statistics include the mean, standard deviation, median, maximum and minimum marks. No. Students is the number of students, No. passed is the number of students that passed and the Pass Rate is the number of students that passed as a percentage.

Figure 3 allows the viewer to see the distribution of marks in different mark brackets. These are done for 10% brackets. i.e. The first marks bracket contains 0-10%, the second 10-20%, up to the tenth and final bracket containing 90-100%. The section underneath contains the total, passed and pass rate which is the same as the last three rows of the table from Figure 2.

The three graphs, of which one example is provided in Figure 4, summarises the information of the distribution between the different marks brackets. The graphs are titled Practical, Exam and Final which yet again refer to those three different categories. The x-axis on the graph gives the numerical values of the different marks brackets while the y-axis gives the number of students who fall into each bracket.

Contents

Chapter 2: Reference Manual

2.1 Array Names and Ranges

Table 3: Array names, where they are defined and their function.

Contents

NOTE: For sections 2.2 and 2.3, The algorithm used is of the general format: =IF(Student_Number<>"";<ALGORITHM;>;"") and
=IF('Raw Data'.A2<>"";<ALGORITHM;>;"") for sections 2.4-2.6. These are if-then-else statements where the very first conditional, before any others, is that there has to be, as shown in sections 2.2 and 2.3, a student number present, and data present in the Raw Data worksheet, cell A2, where the first student record entry would be made, as indicated in sections 2.4-2.6. If these conditions are fulfilled, in addition to any other conditional requirements that may be needed, then the <ALGORITHM> is executed, whatever that may be, otherwise a blank string will be returned to denote a lack of data. Unless otherwise indicated, only the <ALGORITHM> will be shown.

Contents

2.2 Preparing Raw Data for Analysis

In order to return only the relevant data needed for analysis, this algorithm below is applied in the hidden section of the sheet, returning the appropriate score taken from a specific cell x in Raw Data if their Enrolment Status returns a blank string, or an assumed zero in the case of a blank cell where the student is seen to be currently enrolled.

IF(TRIM(Enrolment_Status)="";'Raw Data'.x;"")

Contents

2.3 Submission Sheet

Returning the final score involves the algorithm below, where if trimming an entry under Enrolment_Status returns a blank (suggesting a currently enrolled student), then the calculation for the final score, comprised of 40% for the practical mark, and 60% for the exam mark, is returned, rounded to a whole number. Otherwise, a blank string is returned.

IF(TRIM(Enrolment_Status)="";ROUND(Practical_Mark*0.4+Exam_Mark*0.6);"")

In returning a final grade, if trimming the value in Enrolment_Status returns a blank string, then if the final score is greater than or equal to a certain level, the appropriate grade is returned, otherwise the next grade down is returned if the score is greater than or equal to its minimum specified cut-off, and so on. If trimming Enrolment_Status does not return a blank string then no grade is returned. This is expressed in the below algorithm.

IF(TRIM(Enrolment_Status)="";IF(Final_Overall_Score>=80;"HD";
IF(Final_Overall_Score>=70;"D";IF(Final_Overall_Score>=60;"CR";
IF(Final_Overall_Score>=50;"P";IF(Final_Overall_Score>=45;"N+";
IF(Final_Overall_Score<45;"N";""))))));"")

Contents

2.4 Analysis- Sex, Residency, Degree Programme

In order to return the number of students currently enrolled in the unit who satisfy a certain condition x, the algorithm below is used where SRD can refer to either Sex, Residency or Degree Programme, and x refers to the values that fall under those three categories.

SUM((SRD=x)*(Final_Overall_Score<>""))

Similar to the previous algorithm, this one returns the number of students who satisfy x who also failed the unit.

SUM((SRD=x)*(Final_Overall_Score<>"")*(Final_Overall_Score<50))

Incorporating the first algorithm which gives the number of students enrolled satisfying x, the following algorithm sums the final scores of the students satisfying x and divides it by the number of students to give a mean score for students who satisfy x.

SUM((SRD=x)*(Final_Overall_Score)*(Final_Overall_Score<>""))/SUM((SRD=x)*(Final_Overall_Score<>""))

NOTE: For the analyses in regards to the Degree Programme, it should be noted that in addition to the standing condition of requiring data present, there is the additional condition of needing to be currently enrolled doing course x, that is, to have a final mark returned as indicated by:

=IF(('Raw Data'.A2<>"")*SUM((Degree_Programme=x)*(Final_Overall_Score<>""));...;"")

Contents

2.5 Returning Statistical Values

In order to return certain statistical values, such as a mean, standard deviation, median, maximum or minimum value for a certain assessment mark, the following general algorithm is used, where FUNCTION would be replaced by the appropriate OpenOffice.org function to get the required value, and Assessment Mark refers to Practical_Mark,Exam_Mark or Final_Overall_Score.

FUNCTION(Assessment Mark)

In order to return the number of students who passed a given assessment, the number of students who were given a final mark, and who got a mark equal to or greater than the pass mark of 50 is summed together, as expressed below.

SUM((Final_Overall_Score<>"")*(Assessment Mark>=50))

The pass rate, expressed as a percentage, is determined by simply dividing the number of students who passed by the total number of students, if there is a value in both the number of students and number of students passed. This varies slightly for determining the pass rate among males and females or local and international students in that to determine the total number passed, the number who failed is subtracted from the number of students and divided by the number of students.

Contents

2.6 Determining the spread of students

The below algorithm determines how many students scored between x and y, x inclusive for a certain assessment. It is calculated by summing the number of people who were given a final score, given a score in that assessment, whose mark was greater than or equal to x and less than y.

SUM((Final_Overall_Score<>"")*(Assessment Mark>=x)*(Assessment Mark<y))

The number of students counted in the analysis of the scores for each assessment is determined by summing the number of students who fall into each 10% bracket together, i.e. The sum of each of the outputs from the previous algorithm for an assessment piece.

Contents

Copyright © 2003 Carlo Andreacchio The University of Western Australia