# example ks3 markbook

Document Sample

Excel Video Tutorials

KS3 ICT Activities

A 4 L Resources
Lesson Objectives
This activity will teach you how to do the following skills:
1. Filter data so that it only shows specific groups of students
2. Calculate an average grade using a formula
3. Display high, middle and low achievers using traffic light system

How well can you do the skills below?
3 = without any help    2 = with some hints help
1 = with teacher support

Skill
Type a list of student names into a column
Use autofilter to display specific data
Create a formula to calulate an average
Create a drop down list of possible answers
Hide columns and show them again
Use conditional formatting to colour code cells
Total
o do the following skills:
s specific groups of students
sing a formula
achievers using traffic light system.

some hints help

Score
a column
data
verage
ain
our code cells
Total                          0
Surname             Firstname      Form
Allen               Harriet        7abc
Atkins              Awontemi       7abc           Use Autofilter
Bay                 Olivia         7def           Highlight Columns
De Pina             Nkole          7abc           Filter
Eagle               Samuel         7def           AutoFilter
Fare                Annelies       7abc
Farrow              Ashley         7def
Greiner             Christopher    7def
Harrowing-McGhee    Lewis          7abc
Henwood             Laura          7abc
Hughes              Sarah Lucy     7def
John                Chloe          7def
Johnson             Abraham        7def
Knell               Jhonatan       7abc
Lake                Bethany        7abc
Lawson              Daisy          7def
Lee                 Leo            7def
Mazzilli            Evangelina     7abc
Miller              Benjamin       7def
Otter               Nyasha         7def
Tejeda Ortiz        Liam           7abc
Tom                 Ellie          7abc
Wilson              Lloyd          7def
0              0          0   0           0           0   0   0
0   0   0   0   0   0
Surname         Firstname     Form   Target       7.1         7.2         7.3   Current
Allen              Harriet         7abc              5         4.0         5.0   5.0
Atkins             Awontemi        7abc              4         3.0         3.4   3.4
Bay                Olivia          7def              5         4.4         4.4   5.0
Cunningham         Bradley         7abc              4         3.0         3.4   4.0
De Pina            Nkole           7abc              5         4.0         4.4   5.0
Eagle              Samuel          7def              5         4.7         5.0   5.4
Fare               Annelies        7abc              5         3.0         4.4   5.0
Farrow             Ashley          7def              5         4.0         5.0   4.0
Greiner            Christopher     7def              5         4.0         5.0   5.0
Harrowing-McGhee   Lewis           7abc              4         4.0         3.7   3.0
Henwood            Laura           7abc              5         4.7         5.4   5.4
Hughes             Sarah Lucy      7def              5         4.0         4.7   5.0
John               Chloe           7def              5         4.7         4.4   5.0
Johnson            Abraham         7def              5         4.0         4.7   4.7
Knell              Jhonatan        7abc              5         4.0         4.4   5.0
Lake               Bethany         7abc              5         5.0         4.7   5.0
Lawson             Daisy           7def              5         5.0         4.4   4.7
Lee                Leo             7def              5         5.0         4.7   4.0
Mazzilli           Evangelina      7abc              5         4.4         4.4   4.4
Miller             Benjamin        7def              5         3.0         5.0   5.0
Otter              Nyasha          7def              5         4.0         4.7   4.7
Penfold            Adam            7def              5         4.7         5.0   5.4
Tejeda Ortiz       Liam            7abc              5         3.0         4.7   3.7
Tom                Ellie           7abc              5         4.0         5.4   4.4
Wilson             Lloyd           7def              5         4.0         5.0   4.7
Create an average level
Cell H2
=average(e2:g2)
Fill handle (bottom right of cell)
transfer formula to H26
Surname         Firstname     Form   Target       7.1         7.2         7.3
Allen              Harriet         7abc              5         4.0         5.0   5.0
Atkins             Awontemi        7abc              4         3.0         3.4   3.4
Bay                Olivia          7def              5         4.4         4.4   5.0
Cunningham         Bradley         7abc              4         3.0         3.4   4.0
De Pina            Nkole           7abc              5         4.0         4.4   5.0
Eagle              Samuel          7def              5         4.7         5.0   5.4
Fare               Annelies        7abc              5         3.0         4.4   5.0
Farrow             Ashley          7def              5         4.0         5.0   4.0
Greiner            Christopher     7def              5         4.0         5.0   5.0
Harrowing-McGhee   Lewis           7abc              4         4.0         3.7   3.0
Henwood            Laura           7abc              5         4.7         5.4   5.4
Hughes             Sarah Lucy      7def              5         4.0         4.7   5.0
John               Chloe           7def              5         4.7         4.4   5.0
Johnson            Abraham         7def              5         4.0         4.7   4.7
Knell              Jhonatan        7abc              5         4.0         4.4   5.0
Lake               Bethany         7abc              5         5.0         4.7   5.0
Lawson             Daisy           7def              5         5.0         4.4   4.7
Lee                Leo             7def              5         5.0         4.7   4.0
Mazzilli           Evangelina      7abc              5         4.4         4.4   4.4
Miller             Benjamin        7def              5         3.0         5.0   5.0
Otter              Nyasha          7def              5         4.0         4.7   4.7
Penfold            Adam            7def              5         4.7         5.0   5.4
Tejeda Ortiz       Liam            7abc              5         3.0         4.7   3.7
Tom                Ellie           7abc              5         4.0         5.4   4.4
Wilson             Lloyd           7def              5         4.0         5.0   4.7
Current
4.7
3.3   Insert a new column
4.6   Right Click on column D
3.5   Insert
4.5
5.0
4.1
4.3
4.7   Create a drop down list
5.2   validation
4.6   List
4.7   HAP, MAP, LAP
4.5
4.5
4.9
4.7
4.6
4.4
4.3
4.5
5.0
3.8
4.6
4.6
Surname         Firstname     Form     Ability   Target       7.1         7.2         7.3
Allen              Harriet         7abc     MAP                  5         4.0         5.0   5.0
Atkins             Awontemi        7abc     LAP                  4         3.0         3.4   3.4
Bay                Olivia          7def     MAP                  5         4.4         4.4   5.0
Cunningham         Bradley         7abc     LAP                  4         3.0         3.4   4.0
De Pina            Nkole           7abc     MAP                  5         4.0         4.4   5.0
Eagle              Samuel          7def     HAP                  5         4.7         5.0   5.4
Fare               Annelies        7abc     MAP                  5         3.0         4.4   5.0
Farrow             Ashley          7def     MAP                  5         4.0         5.0   4.0
Greiner            Christopher     7def     MAP                  5         4.0         5.0   5.0
Harrowing-McGhee   Lewis           7abc     MAP                  4         4.0         3.7   3.0
Henwood            Laura           7abc     HAP                  5         4.7         5.4   5.4
Hughes             Sarah Lucy      7def     MAP                  5         4.0         4.7   5.0
John               Chloe           7def     MAP                  5         4.7         4.4   5.0
Johnson            Abraham         7def     MAP                  5         4.0         4.7   4.7
Knell              Jhonatan        7abc     MAP                  5         4.0         4.4   5.0
Lake               Bethany         7abc     HAP                  5         5.0         4.7   5.0
Lawson             Daisy           7def     MAP                  5         5.0         4.4   4.7
Lee                Leo             7def     MAP                  5         5.0         4.7   4.0
Mazzilli           Evangelina      7abc     MAP                  5         4.4         4.4   4.4
Miller             Benjamin        7def     MAP                  5         3.0         5.0   5.0
Otter              Nyasha          7def     MAP                  5         4.0         4.7   4.7
Penfold            Adam            7def     HAP                  5         4.7         5.0   5.4
Tejeda Ortiz       Liam            7abc     LAP                  5         3.0         4.7   3.7
Tom                Ellie           7abc     MAP                  5         4.0         5.4   4.4
Wilson             Lloyd           7def     MAP                  5         4.0         5.0   4.7
Current
4.7
3.3   Hide a column
4.6   Right click columns F-H
3.5   Select Hide
4.5
5.0
4.1
4.3
4.7
3.6
5.2
4.6
4.7
4.5
4.5
4.9
4.7
4.6
4.4
4.3
4.5
5.0
3.8
4.6
4.6
Surname         Firstname      Form     Ability   Target       Achieve   Current
Allen              Harriet         7abc      MAP                  5                       4.7
Atkins             Awontemi        7abc      LAP                  4                       3.3
Bay                Olivia          7def      MAP                  5                       4.6
Cunningham         Bradley         7abc      LAP                  4                       3.5
De Pina            Nkole           7abc      MAP                  5                       4.5
Eagle              Samuel          7def      HAP                  5                       5.0
Fare               Annelies        7abc      MAP                  5                       4.1
Farrow             Ashley          7def      MAP                  5                       4.3
Greiner            Christopher     7def      MAP                  5                       4.7
Harrowing-McGhee   Lewis           7abc      MAP                  4                       3.6
Henwood            Laura           7abc      HAP                  5                       5.2
Hughes             Sarah Lucy      7def      MAP                  5                       4.6
John               Chloe           7def      MAP                  5                       4.7
Johnson            Abraham         7def      MAP                  5                       4.5
Knell              Jhonatan        7abc      MAP                  5                       4.5
Lake               Bethany         7abc      HAP                  5                       4.9
Lawson             Daisy           7def      MAP                  5                       4.7
Lee                Leo             7def      MAP                  5                       4.6
Mazzilli           Evangelina      7abc      MAP                  5                       4.4
Miller             Benjamin        7def      MAP                  5                       4.3
Otter              Nyasha          7def      MAP                  5                       4.5
Penfold            Adam            7def      HAP                  5                       5.0
Tejeda Ortiz       Liam            7abc      LAP                  5                       3.8
Tom                Ellie           7abc      MAP                  5                       4.6
Wilson             Lloyd           7def      MAP                  5                       4.6
I have inserted a new column in F to
identify who are the high and under
achievers

In Cell F2
=e2-j2
Then transfer the formula
Surname         Firstname   Target       Achieve      Current
Allen              Harriet                  5          0.3             4.7
Atkins             Awontemi                 4          0.7             3.3
Bay                Olivia                   5          0.4             4.6
De Pina            Nkole                    5          0.5             4.5
Eagle              Samuel                   5          0.0             5.0
Fare               Annelies                 5          0.9             4.1
Farrow             Ashley                   5          0.7             4.3
Greiner            Christopher              5          0.3             4.7   To colour code achievers.
Harrowing-McGhee   Lewis                    4          0.4             3.6   Highlight F2-
Henwood            Laura                    5         -0.2             5.2   Highlight F2-
Hughes             Sarah Lucy               5          0.4             4.6   conditional formatting.
John               Chloe                    5          0.3             4.7   Copy screenshot
Johnson            Abraham                  5          0.5             4.5
Knell              Jhonatan                 5          0.5             4.5
Lake               Bethany                  5          0.1             4.9
Lawson             Daisy                    5          0.3             4.7
Lee                Leo                      5          0.4             4.6
Mazzilli           Evangelina               5          0.6             4.4
Miller             Benjamin                 5          0.7             4.3
Otter              Nyasha                   5          0.5             4.5
Tejeda Ortiz       Liam                     5          1.2             3.8
Tom                Ellie                    5          0.4             4.6
Wilson             Lloyd                    5          0.4             4.6
To colour code achievers.
Highlight F2-F26 and fill in yellow.
conditional formatting.
Copy screenshot
Surname         Firstname   Target       Achieve      Current
Allen              Harriet                  5          0.3          4.7
Atkins             Awontemi                 4          0.7          3.3
Bay                Olivia                   5          0.4          4.6
De Pina            Nkole                    5          0.5          4.5
Eagle              Samuel                   5          0.0          5.0
Fare               Annelies                 5          0.9          4.1
Farrow             Ashley                   5          0.7          4.3
Greiner            Christopher              5          0.3          4.7   To unhide columns
Harrowing-McGhee   Lewis                    4          0.4          3.6   Highlight the columns either side
Henwood            Laura                    5         -0.2          5.2   the hidden columns i.e. F
Hughes             Sarah Lucy               5          0.4          4.6   Right click and unhide
John               Chloe                    5          0.3          4.7
Johnson            Abraham                  5          0.5          4.5
Knell              Jhonatan                 5          0.5          4.5
Lake               Bethany                  5          0.1          4.9
Lawson             Daisy                    5          0.3          4.7
Lee                Leo                      5          0.4          4.6
Mazzilli           Evangelina               5          0.6          4.4
Miller             Benjamin                 5          0.7          4.3
Otter              Nyasha                   5          0.5          4.5
Tejeda Ortiz       Liam                     5          1.2          3.8
Tom                Ellie                    5          0.4          4.6
Wilson             Lloyd                    5          0.4          4.6
To unhide columns
Highlight the columns either side of
the hidden columns i.e. F-J
Right click and unhide
Skill Scores
Look at the scores you gave at the start of the lesson.
Give yourself a new score to show how confident you are now.

How well can you do the skills below?
3 = without any help    2 = with some hints help
1 = with teacher support

Skill                      First Score       New Score
Type a list of student names into a column                         0
Use autofilter to display specific data                            0
Create a formula to calulate an average                            0
Create a drop down list of possible answers                        0
Hide columns and show them again                                   0
Use conditional formatting to colour code cells                    0
Total                     0               0

What are the advantages of using an electronic markbook?

Now play zombie boxing

DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
 views: 21 posted: 9/15/2012 language: English pages: 18