My users are the tutors for all years. The tutors need a system in which to enter attendance marks to analyse the attendance of their students. The spreadsheet should enable them to enter the attendance marks for each pupil

My Plan

I will have twenty six sheets in my workbook.  Each sheet will contain the names and data for the twenty six tutor groups and each will have its own results graphs.

I will use the following functions:

=Average - to work out the pupils attendance averages

I will use the =IF function to see what Their awards will be.

I will use graphs to compare the attendance’s of each set.

I am now ready to start creating my spreadsheet workbook.

HOW I CREATED MY SPREADSHEET

I first loaded Microsoft® Excel 97 and a blank workbook was opened.  I saved this into my user area and named it “Attendance Register”.

I started to rename the sheets by right clicking on the tabs.

I started to enter my data into the spreadsheet.

I entered the column headings and changed the alignment by changing the text alignment to centre horizontally and vertically.

I entered the names of the pupils and their attendance marks for the class. I changed the font size of the headings and made them bold so that they were clear and easily identified.

I decided to put borders around the text to make it clearer and easier to read and changed the row height, merged cells and changed column widths, using the format menu.

The Average

To work out the average attendance I used the average function. I clicked on the = sign on the toolbar and chose “Average” from the list of options.

Then I clicked on the R17 block and entered =average(R6:R16) and then clicked enter.

Equivalent Percentage

To convert it into a percentage I selected R17 and right clicked on it then I selected format cell on the pop up menu. Then I clicked on number at the top of the bar and selected percentage on the options bar.

 

Awards

To work out the awards I needed to know what amount of attendance's gave which award. My next function which was the “IF” query.

To work out the awards I entered the following IF queries:

Gold award: =IF(Q6>11,"Gold Award","-")

Silver award: =IF(Q6<8,"-",IF(Q6<12,"Silver Award","-"))

Bronze award: =IF(Q6<5,"-",IF(Q6<8,"Bronze Award","-"))

E.g. If you wanted to enter the bronze award you would click on cell S6, which is the first cell on the bronze award row. Enter the formula above then press enter. Finally you need to click on the small black box on the right-bottom corner of the cell and drag it down to fill the whole of the bronze award boxes.    

Graphs

Graphs allow people to just glance at information and understand it so it is a good idea to have them in your spreadsheet.

To put graphs onto the spreadsheet you must first highlight all of the information you want to enter into the graph then click on the chart wizard button on the toolbar.

When the chart menu appears you can select what type of graph you would like, I chose the bar chart menu and selected the 3D bar chart option then pressed next.

Then you can enter your axis titles and chart title on the next screen. I named by bar chart “Award Chart” and labelled the Z-axis “Number Of Pupils”, then pressed OK.

After I pressed OK a box titled chart location appeared, in this box I clicked on “As object in Attendance” then pressed finish.

Then your chart will be complete.