Third test score - The Score Sections have been used to be able to get an average test score, percentage test score and grade.
Average – This has been used by taking all the test scores and working out an average score for all of them.
Percentage score – I have taken all the test scores and used a formula to work out a Percentage.
Pass / Fail – This has been done by using an IF Formula.
Grade – This has been done by using a Look Up Formula.
Now that I had planned my work I could get onto making it!
The Beginning
First I had to open Microsoft excel 2000.
Once the program had opened I started to make it. I put all the data topics in straightaway and started making up and putting in the Primary Data e.g. Name, Age, Attendance, Test Scores.
Resizing cells
When writing in data, sometimes the cells are two small for the digits or text.
To do this it is very simple. I put my mouse cursor over the Cell Section lines and held down my mouse button and moved it left or right or up or down but I would only have to do it on these lines as these cells are two small.
Now that I had solved this problem I could finally write in all the Primary data.
The Functions of Excel
There are some things that are needed to know to be able to use formulae easily and quickly. Some functions on Excel are there to help us. They enable us to be able to create, sort-out, and manipulate data very quickly and very easily. These are the main controls I used in Excel.
- This control is called Auto Sum. It will add up all the columns above the highlighted cell. This comes in very handy is you want to add up a lot of data quickly.
- This control is called Function. When you press it the screen shown on the right would appear.
It gives a list of all the possible formulae in excel and will take you through a creation wizard to make the formula you desire.
- This control is called sort (ascending). It puts all the highlighted data in, alphabetical order, but if you select 2 columns or more it will sort everything in alphabetical according to the first column highlighted.
- This control is the complete opposite of the sort ascending control, and is called sort descending and will sort everything (surprisingly) in descending alphabetical order.
There are two main types of formulae in excel a mathematical formula and a function formula. Mathematical formulas consist of just maths, E.g. =5+2 Function Formulae usually have a word at the beginning and then the data it wishes to work out,
E.g. = SUM(B2,B3) This formula would add the two cells up and give an answer.
Also if you notice that there is an Equals Sign (=) at the beginning of any formula. This tells Excel that a formula is to be created.
The Formulae
I am now going to write in the formulae using the Secondary Data. I will go through each one, one by one.
Average
For this formula I used a button, designed for excel, to take you through a step-by-step process to make a formula for anything needed. This is the button I used It is called a ‘Formula Function’ button.
Once clicked it opens a window with all the different types of formulae that can be made using Excel. I just wanted a formula to work out the average, so I went down to average on the list and pressed Ok-
Percentage
For this formula I had to use some of my own maths knowledge.
To work out a percentage of a score like 30/60 I would divide 30 by 60 which would equal 0.5 then I would times that number by 100 to get its percentage.
That’s what I had to do for this formula I used the same principle to work out the percentage for it but because I want a percentage for the total amount of test results I had to add all the scores together. This is the formula I came up with.
*Note- this is the formula I used in the markbook.
A common mistake with writing formulas by hand is that people forget to put an equals (=) sign in. If this is not put in at the beginning Excel will not read it as a formula.
If Formula
An ‘If’ formula will look at data and give a separate answer whether it’s true or false for example, I want to find out if someone has passed or failed a test, I would use this to look at the data of a pupils total test score and write Pass or Fail depending on how high the score was.
This is the formula I used in the Mark book
I started the formula off with an Equals Sign ‘=’ then put the name of the formula ‘IF’ I then opened up some brackets ‘(‘
I then put in a logical test which is ‘J4>80’. Now all I have to do is put in my Variables (Pass/Fail) I have to open Speech marks at the beginning and at the end of each variable and a comma in between the two. Like this “Pass”,”Fail” All I have to do now is close the brackets.
This is what the computer reads it as –
If cell J4 is bigger(>) than 80 then write pass, If that is false then write Fail.
Look up Tables
A look up table is a formula that returns a value either from one row or one column range or from an array. The LOOKUP function has two syntax forms: vector and array. The vector form of LOOKUP looks in a one-row or one-column range (known as a vector) for a value and returns a value from the same position in a second one-row or one-column range. The array form of LOOKUP looks in the first row or column of an array for the specified value and returns a value from the same position in the last row or column of the array.
In my project I have used a look up table to give a grade depending on a score of a pupil.
First of all, to go about creating this formula I had to create the lookup table. The table shown on the right is the one I have used in my project. After doing this I went about creating a formula. The one shown below is the one I have used.
Rounding Formula
Rounding formulas will take a number in a cell and round it up to how many digits I would want. I needed to use this in this project because excel will give a decimal number up to 6 decimal places and when using an average formula the answer is rarely a whole number.
So this makes the data in the cells easier to read and under stand. The pictures shown on the left show the advantages;
BEFORE AFTER
More about formatting cells
Colour
You can change the colour of the cells, the text and the borderlines of the cells. This won’t change any of the data but will make the cells more attractive and colourful to look at.
This button will change the colour of the cells.
To change the colour of the cells you would have to highlight the selected cells and press the button on the left. If you press the arrow on the right of the button, a list of colours will appear which allows you to choose your own colour.
This button will change the colour of the borderlines of a cell.
To do this you would highlight the area you would like to change and press the control on the right.
Press the arrow on the right to select a colour.
This button changes the colour of the text or digit in a cell.
To do this highlight the cells which contain the text and press the button on the left
Press the arrow on the right to select a colour.
How to change the font and size of the text
This is also very simple
This control will change the font. Highlight the text in the cell that you want to change and click on the arrow on the right, a list of fonts will appear, then you just choose one. If you choose the font before selecting a cell, it will write in that font until told otherwise.
This control will change the font size. Highlight the text in the cell that you want to change and click on the arrow on the right, a list of numbers will appear, then you just choose one. The higher the number the bigger the font will be. You can choose your own font also by typing it into the box. If you choose the font size before selecting a cell, it will write in that font size until told otherwise.
This control has 3 buttons incorporated into it. The B will make any highlighted text BOLD. The I will make any highlighted text Italic (will slant it).
The U will Underline any highlighted text
If you choose one of the three buttons above before selecting a cell, it will make the text that way until told otherwise.
Graphs
Graphs are another way to view data quickly and easily. Using excel you can make as many types of graphs, from a simple bar chart to donut charts. I have made two types of graphs. I’ve only made two, as there are only two graphs, which would be relevant as some graphs go better with different information for example;
- Column Chart – I have used this to view everyone’s attendance and age.
- Scatter Graph – I have used this to view the average test scores of everyone. It plots points on a graph and lets you view the average of everyone’s test score.
I couldn’t have used a Pie Chart to view someone’s average test score because there would be too many sections all closed in together but a Pie Chart could be used to see a percentage of how many people passed or failed.
These are the pictures of the charts I have used in this project;
Which do you think is clearer?
When looking at these two types of data, which do you think is the clearer?
I believe that the graph is clearer because I can look at the graph and tell straight away that the average score of everyone is over 20. If I had to look at the table to work this out it would take me a while because I would have to go through each score and examine it one by one.
How to make a graph
Graphs are slightly complicated to make because you have to know what graph you want to make before you write in the data, which I didn’t do so it made it very hard to choose suitable data to make graphs from.
Well after deciding what graph you would like to do, you begin to write in the data. You must do it with the two axis titles at the top and the information underneath, as shown below.
In this project, as well as a scatter graph, I have also made a column chart. The chart is based on attendance
It will open another window, which contains a list of graphs and charts as shown on the left, all you have to do is choose one-
If you want to preview what the graph will look like before you make it, you would press the control which says ‘Press and Hold to View Sample’, it will then come up with a picture of the graph about be created.
Then all you have to do is press finish.
Evaluation
In this project I have created an electronic mark book for a teacher.
It includes 5 formulae they are: Average
Rounding
Percent
If
Lookup
What I could have done better
In this project I think I should have considered the layout of the excel document more carefully because when it came to making the graphs the information needed was inaccessible to highlight without highlighting other data as well.
I have also learnt that I should save work every 15 minutes because at one point I had finished all my work but I hadn’t saved it, so now I’ve started saving backups and saving my work every 15 minutes.
I have learnt that most graphs do not work with most data and that I should have considered more carefully about what graphs I used in this project.
