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

Spreadsheet report - intended for a science teacher teaching 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 Report The spreadsheet is intended for a science teacher teaching 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. He would like to know if a computer could aid him to process the data from module tests that are being taken this year. He would like to know whether the pupils are on target or not, the grades gained from the tests, the total marks and grades, and the average, minimum and maximum marks and grades. He would also like the computer to help him to keep track attendance. He would like to know the total attendance of the members of his class. When complete, the spreadsheet will automatically calculate the grade of each module test, and display whether the pupil is working at heir level. It will also calculate the total marks and average grade of each pupil, and for the entire class. This will be shown on one sheet, whilst on another, the total number of attendances and the total attendance as a percentage over a whole term will be shown. This will be useful for the teacher so as he can monitor his pupils and help those who are under performing easily. In order to do this, I needed to plan out the spreadsheet. I worked out what sheets and tables I needed, and figured out which functions and formulae I would be using to calculate with. I planned to have two sheets, Tests and Attendance. The Tests sheet contains three tables. The first is the Results table and it contains the results of the science module tests. The pupil's surname and forename are located in the first two columns, and their expected mark in the third column. The data relating to each separate test is located in three adjacent columns, with a merged cell above tying them together. There are four of these next to each other, one for each module. The first column in the group of three is called Marks, and this contains the actual score from each test. The next column is called Grade and this shows the grade corresponding to the mark. This grade is obtained from the second table, called Points, which contains the points and grading system. Using the VLOOKUP function does this. The cell references here must be absolutes. The third and final column is called Attainment.