My design brief for this project was to produce a spreadsheet that would make it easier for a teacher to access data about his or her pupils.
This spreadsheet was to include a register containing the pupils’ full names, sex, and date of birth. It was to include also a markbook, and a sheet of graphs showing the pupils’ grades. In the register, there was to be a record of the pupils’ full names, date of births, gender, and the amount of times they attended school. After I had entered the attendance of the students, I entered a formula that would automatically calculate the attendance to date of the student and the percentage of attendance.The formula for attendance is =SUM(E2:AB2) and the formula for the percentage of attendance is =AC2/22.
To save time, after entering the formula for the first cell, I put my cursor over the square in the corner of the cell outline and dragged it down the cells that followed so that the inputted numbers would immediately calculate the said records. The information that I put into my spreadsheet (names, etc. ) was a mixture of my friends from school, my favourite singers, and footballers.If this spreadsheet was being made and used by a teacher, then the information would be of the pupils, which would be gathered from the school records.
In order to make the best markbook; it needed to include the marks the pupils obtained in each module. It also had to have the same information (e. g. Surname, forename, etc.
) from the register, plus the marks from their modules, and an average, maximum, minimum, and range of the marks obtained. For the marks of the students, I chose the highest mark for the modules as 16, and gave everyone a mark out of 16.I did this with all of the other modules, and for each pupil. To then find the average, I had to entered the formula =AVERAGE(E1:E10) so that all the marks in cells E1 to E10 would have been calculated to find the average. I did this for the following cells (cells F-K).
To find the maximum, I had to enter the formula =MAX(E1-E10), to find the minimum, I entered =MIN(E1-E10), and for the range I entered =E16-E18. This then helps when trying to calculate the grade that should be given. This screen shot shows the formulas to find the average, maximum, minimum, and range of marksWhen trying to determine the amount of different grades given, the following method is used. First, the grades “Below pass”, “Pass”, “Merit”, and “Distinction” are applied. To calculate “Below Pass”, the formula =COUNTIF(L2:L10,”Below Pass”) is used. To calculate “Pass”, “Merit”, “Distinction”, you use the same formula, but change “Below Pass” to the next grade level.
The number of below passes, passes, merits, distinctions should automatically be calculated. To know what percentage of marks their pupils are getting, a teacher needs to have graphs of the grades that were gained during a module.I used the last page of the spreadsheet for showing graphs. A graph to show the marks the students obtained.
A Pie chart to show the grade the pupils obtained To make this graph page, I first chose to use a bar graph for the marks, and a pie chart for the grades. To make the bar graph I entered all the information for the x-axis and all the information for the y-axis. On the x-axis I put the names of the students and then on the y-axis, the number of marks, going up in two’s (2,4,6,8,etc).The computer automatically calculated the height of the bar for each student; for example the height of the bar for Mr.Owen is greater than the bar for Ms. Edwards because he got more marks than she did. I chose to use the bar graph to show the marks because it would show which students are more likely to get which grades because of the marks they got. For instance, someone looking at the bar graph would notice that Ms.
Kempton’s bar is greater in height than Ms. Hawkes’ so that would mean Ms. Kempton got better marks in the modules than Ms. Hawkes, therefore implying that Ms. Kempton got a better overall grade.