Spreadsheet report - intended for a science teacher teaching a Year 10 :: Computer Science

Spreadsheet report - intended for a science teacher learn a Year 10 class in a secondary school. He is having difficulty managing all of the data concerning the marks and attendance of class 10X2.Spreadsheet ReportThe spreadsheet is intended for a science teacher teaching a Year 10class in a secondary school. He is having difficulty managing all ofthe data concerning the marks and attendance of class 10X2. He wouldlike to know if a computer could aid him to process the data frommodule tests that are being taken this year. He would like to knowwhether the pupils are on target or not, the grades gained from thetests, the total marks and grades, and the average, minimum andmaximum marks and grades. He would also like the computer to help himto keep lead story attendance. He would like to know the total attendanceof the members of his class.When complete, the spreadsheet will automatically calculate the gradeof each module test, and display whether the pupil is working at heirlevel. I t will also calculate the total marks and average grade ofeach pupil, and for the entire class. This will be shown on one sheet,whilst on another, the total number of attendances and the totalattendance as a percentage over a whole term will be shown. This willbe useful for the teacher so as he can monitor his pupils and helpthose who are under performing easily.In order to do this, I needed to plan out the spreadsheet. I workedout what sheets and tables I needed, and figured out which functionsand formulae I would be using to calculate with. I planned to possess twosheets, Tests and Attendance. The Tests sheet contains three tables.The first is the Results table and it contains the results of thescience module tests. The pupils surname and forename are located inthe first two columns, and their evaluate mark in the third column.The data relating to each separate test is located in three adjacentcolumns, with a merged cell to a higher place tying them together. There are fourof t hese next to each other, one for each module. The first column inthe group of three is called Marks, and this contains the actual slayfrom each test. The next column is called Grade and this shows thegrade corresponding to the mark. This grade is obtained from thesecond table, called Points, which contains the points and gradingsystem. Using the VLOOKUP function does this. The cell references heremust be absolutes. The third and final column is called Attainment.