This spreadsheet is designed for the use of the mathematics teacher to test the results of his/her pupils. It will enable the mathematics teacher to monitor the progress and to predict the results that the students will gain in the test.

The formula needed in the thirteenth column:

Is [=IF(K2>50,"pass","fail")]. This is the simpler version of the formula above. This is because when you work out a prediction, you only need to know whether the students should pass or fail according to the results achieved in previous tests.

This is the bar chart showing the results achieved in the examination by the students. It is appropriate to the mathematics teacher because it will show him/her clearly the proportion of students who gained each result.

This is the pie chart showing the predictions of what the student should get in the next examination using the results already achieved. It is appropriate to the mathematics teacher because it will show him clearly the proportion of students who will need help to achieve at least a pass in the next examination.

This is a short step-by-step guide to setting up a spreadsheet in Microsoft Excel.

Shortly after clicking on the ‘Microsoft® Excel’ Icon, you should see a similar screen to the one below:

This is just the screen that tells you that Microsoft Excel is loading up.

It shows you the version of Microsoft Excel installed on your computer. It also shows you who the product is licensed to and some legal information.

Once Microsoft Excel has loaded up, a new spreadsheet appears on the page. And, depending on the version of Microsoft Excel installed on your computer, an options list should appear on the right hand side of the page:

If none of the options apply to you, you can close the options box by clicking on the ‘x’ icon on the top right-hand corner of the options box. Now you are left with a blank spreadsheet to work on. Each of the rectangular sections of this spreadsheet is called a cell. A spreadsheet is made out of rows and columns. The rows go across and the columns go downwards. The numbers down the side are the row numbers and the letters along the top are the column numbers. We can use these numbers and letters to produce cell references. Cell references are used to identify a particular cell in a spreadsheet. For example, the cell highlighted in the spreadsheet has the cell reference A1. The part of the spreadsheet that you use for your data is called a grid. You can add data into a cell by, first of all, highlighting a cell, and then typing in the data. You can see what you are typing and also make amendments by using the formula bar which is situated right above the spreadsheet and is labelled with the letters ‘fx’. Now you should be able to start entering data and producing spreadsheets.

Now I will show you how I produced my spreadsheet for the mathematics teacher.

First of all, I opened a blank spreadsheet in Microsoft® Excel and entered all the data into the first row and columns A-I:

I have entered the data that I was given onto the grid but the column widths look wrong because you can see a lot of blanks and there is a lot of wasted space. Also, the headings for the columns and the data in columns E-I are currently aligned to the left. I think that the headings and the data in columns E-I will look better if they are centred. I will use some formatting techniques to improve the presentation of the grid:

Now you can see that the spreadsheet looks much better than before I applied the formatting techniques. First of all, I double-clicked between each of the column letters to make the column widths just the right size for the data in them and then I highlighted the column headings and columns E-I and clicked on the ‘centre’ icon:

I then had the task of working out the averages, totals, grades and predictions of all the students’ exam results. I did this with the help of formulas (see page 3).

First of all, I had to add the formulas needed into the first row of the column where the formula was to be used:

To apply the same formula for a whole column, I highlighted the cell where I entered the formula, I placed my mouse cursor on the bottom right-hand corner of the cell until the mouse cursor turned into a ‘+’ sign and then drag down to the bottom row of the column.

I then used some more formatting techniques: I centred the average and total columns and I AutoFit® the last four columns (double-clicking between the column letters to make the columns just the right size).

Right now, I thought that the spreadsheet looked a bit dull, so I thought I would brighten it up by adding some colour to it:

I added colour by highlighting all the cells that I wanted to colour pale blue, then clicking on the fill icon (as briefly explained on page 2) and then choosing the desired colour, then for sky blue followed through the same procedure. For the top row with the column headers, I used light blue, but the black font did not show up very clearly so I changed the font colour. I did this by highlighting the top row of the grid and then clicking on the ‘font colour’ icon (which is situated to the right hand side of the fill icon) and then I chose my desired colour which was light blue. When I changed the colour of the grid, I realised that the grid lines had disappeared. This made the grid a little more difficult to read, so I decided to move on to some more advanced formatting:

I clicked on the format tab from the toolbar, I clicked on cells, and then this new window appeared:

First of all, I clicked on the ‘border’ tab in the new window, then I chose the type of line from the selection on the right, then I clicked on all the parts of the cell where I wanted the lines to be placed in the middle part of the window and then I clicked on ‘ok’. As you can see in the top part of the foreground window in the screenshot, by using the same window, you can change the number formats (whether you want the numbers to be a form of currency, date, time, percentage, fraction, etc.), you can change the text alignment, you can format the font of the text in each cell, you can use cell shading patterns, and you can choose to protect the spreadsheet.

This is how my grid looks after I have added the grid lines, but if you look carefully, you will notice that the data is now sorted into groups of male and female, males on top and females at the bottom, you will also notice that in each group, the grades are also sorted in ascending order. I clicked on ‘data’ on the toolbar and then clicked on sort:

If you want to sort your data into groups, or sort your data values highest first or vice versa, then you will need to use the ‘data sort’ function in Excel:

If you wish to use this function, then you need to click on ‘data’ and then ‘sort’. Once you click on ‘sort’, this window appears, and there are three drop-down menus in this new window. In each one are the names of all the column headings. From each drop-down menu, you have to choose the column heading by which you will sort your data. Next to the drop-down menus are whether you would like the data to be sorted in ascending or descending order. After you have made your selections, you have to click on the ‘ok’ tab. I wanted to sort my data, first of all, into groups of male and female, so from the first drop-down menu, I chose ‘gender’. I wanted to put males at the top so I chose to sort the data in ascending order. I then wanted to sort the male and female grades in ascending order, so I chose ‘total’ from the drop-down menu. I clicked on descending because I wanted to put the highest grades at the top of the grid. You can see my selections more clearly on the screenshot at the bottom of page 12.

If there are some cells in your grid in which you will only want certain type of numbers, e.g. whole numbers, decimals, dates, time, etc. then you will need to use the ‘data validation’ function in Excel:

To use this function, first you have to highlight the data you wish to validate and then you have to click on ‘data’ and then ‘validation’. Once you click on validation, this window appears. First of all, you have to choose what kind of data you are going to allow from the first drop-down menu. I had highlighted the test marks of the students for the five weeks so I needed to allow whole numbers between 0 and 20 only as 0 was the lowest mark possible and 20 was the highest. As you can see from the screenshot, you can, from the same window, create an input message for when you highlight a cell which has been chosen for validation, you can also create an error message for when an incorrect type of data is entered into the cell.

If you want to highlight important data in one or more of the columns in your grid, then you have to use the ‘conditional formatting’ function in Excel:

To use this function, first you have to highlight the cells in which the cells you wish to highlight are in, then you have to click on ‘format’ and then ‘conditional formatting’. Once you click on ‘validation’, this window appears. First of all, you have to choose whether it is just the cell value that you wish to highlight or a whole formula; you can make your selection in the first drop-down menu. Then, in the second drop-down menu, you can choose whether the data or formula is between, equal to, greater than, less then, etc. then, in the space next to the second drop-down menu, you have to type in the data value that you wish to highlight. Then you have to choose how you wish to highlight the data. You can do this by clicking on ‘format’. Here, you can choose the font, font colour, underline, etc. once you have done this you can click on ‘ok’. I chose to highlight all the distinctions in the column I highlighted and I chose to make the font colour blue.

As you can see on page 4 of this report, I constructed two different charts showing the results of the students and the predictions of the students. I did this by, first of all, making new tables in a new sheet (You can make a new sheet by simply clicking on sheet 2 at the bottom of the grid). My first table had the column headers grade and number of results. My second table had the column headers prediction and number of results. I filled in the values, I highlighted the table that I was going to use and then I clicked on the ‘chart wizard icon’.

The ‘chart wizard’ icon is situated on the same row as the ‘alignment’ icons:

The chart wizard helps you create charts of all kinds. When you click on the chart wizard icon, this window comes up:

What you have to do here is choose the type         What you have to do here is choose

of chart that you want and then click on ‘next’.     whether you want the data in rows or

                                                                                columns and then click ‘next’.                                                                          

     

What you have to do here is choose the chart        What you have to do here is choose

options by navigating through the categories        where you want the chart to be placed

at the top and then click ‘next’.        and then click ‘next’.

This is my finished product. You will notice that I also used the conditional formatting function in the last column and I chose to highlight all the fails in red. It was important to predict the results of the students because this will help to encourage the students to gain a better grade in the next exam. I am pleased with the end product and I hope that the mathematics teacher will find the data useful and also clear and easy to read.

This is simply the ways in which I kept backup copies of the spreadsheet and how I check my data and system for errors

I keep backup copies of my spreadsheet by saving my work on a regular basis. I can save my work by clicking on ‘file’ in the toolbar, then clicking on ‘save as’ and then choosing either a folder on your computer or a disk to save into.

I check my data and system for errors by using a program called ‘Norton System works 2003’:

This is a very reliable program and it keeps your computer running smoothly.

These are the steps I took to keep my work accurate and safe.

By taking part in this project, I feel that I have learnt an awful lot. These are just some of the things I learnt. First of all, I learnt how to take screenshots of what I was doing, I learnt how to make charts, I learnt about conditional formatting, I learnt about data validation and I also learnt some useful new formulas.

I also experienced some ups and downs while working through this project, once, I saved my work to floppy from my home computer, and the work just didn’t open up. I wasted a whole I.C.T lesson because of this and I came to the conclusion that I hadn’t saved my work properly or completely.

I think that this spreadsheet will prove very useful for the mathematics teacher because it is clear, easy to read, and it contains information very useful for reviewing the successes and requirements of his/her mathematics students.