Contents
Step 1
(click here if you want to go
back to contents)
Set up a chart as shown below (Figure 1). This is just a simple example using 3 students. In a real situation, there would be more students and the A column would extend much farther down. To learn more about inputting data and excel capabilities click here .
Step 2
(click here if you want to go
back to contents)
After setting up the chart, input the marks for
each assignment. For example if Andrew's mark for assignment 1 is 78%,
click on the B2 cell, type in 78 and click the enter button. For reasons
of simplicity , only input percentages into the cells. In the following
example (Figure 2), the other cells will have
percentages inputted.
Step 3
(click here if you want
to go back to contents)
To calculate averages per assignment, click
into the cell that you want to calculate the average for. Now you must
input a formula:
1. press the equal button so that the formula bar knows that you
are going to do a calculation. Than type in sum( [see
Figure 3a]
3. Close the brackets and press ENTER to get a result. Notice
in the following example that it does not round up and leaves the calculation
as 76.666.... This tutorial, as noted before is intended to teach you the
basics of using excel for the specific task.
4. To save time and calculate the rest of the average marks per assignment, click on the little box at the lower-right corner of the cell in which you just calculated the average and drag the mouse to the right. (Figure 4) This is known as the fill-right command. What this does is it applies the formula from the chosen cell and uses it on selected cells while altering cell locations in the original formula to suit the selected cell.
5. Release the button and you should see averages for each
of the assignments and tests.
Step 4
(click here if you want to go
back to contents)
There are various calculations to calculate total marks for each individual. Depending on the weight of each assignment or test, different calculations will be used.
In the following example based on previous diagrams let us assume
that:
Assignment | Weight (%) |
Assignment 1 | 10 |
Midterm Test 1 | 10 |
Assignment 2 | 10 |
Midterm test 2 | 10 |
Assignment 3 | 10 |
Labs | 10 |
Final Exam | 40 |
=b2*10+c2*10+d2*10+e2*10+f2*10+g2*10+h2*40
the * sign means multiply.
After the total mark for one cell is complete, we can use the cell to get the rest of the total marks for each student by clicking on the lower-right box on the cell and dragging it down. This is similar to the Fill-right command instead this is known as the Fill-down command.