Calculating % of pupils in certain categories
Hi
I have just had one question brilliantly answered here, and another challenge faces me! I am basically creating a spreadsheet of the attainment of pupils in the class - % of how many achieve level 3, 4 and 5. Of a normal class there will be children we have to look at more closely, such as children with additional learning needs, children who receive free school meals etc etc. What I am trying to create is a spreadsheet that could easily show the percentage of the class that receive level 3,4 or 5, but also how many pupils on the free school meals register attained a level 4 etc. There are a number of different categories. I can't seem to get my head around the layout of the spreadsheet let alone the formulas involved!
Here hopefully is an example of the initial table -
Name |
FSM | ALN |
LC |
CT |
MAT |
L4/5 |
SP |
EAL |
ATT |
CA |
Eth |
Eng O |
Eng R |
Eng W |
Eng |
Wel O | Wel R |
Wel W |
Welsh |
Maths |
Science |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
D Duck | y | y | n | n | y | n | y | n | y | y | y | 4 | 4 | 4 | 3 | 4 | 3 | 3 | 5 | 4 | 4 |
M Mouse | y | n | n | y | n | y | n | n | n | n | y | 4 | 3 | 3 | 4 | 4 | 5 | 4 | 4 | 3 | 5 |
Goofy | n | n | n | y | n | y | n | y | n | n | n | 4 | 3 | 3 | 4 | 5 | 4 | 5 | 3 | 5 | 3 |
Pluto | n | n | n | n | n | n | y | y | n | y | n | 3 | 4 | 5 | 5 | 3 | 3 | 4 | 4 | 4 | 4 |
Daisy | n | y | y | y | n | n | n | n | y | y | n | 5 | 5 | 4 | 4 | 5 | 4 | 3 | 5 | 3 | 5 |
So to explain a bit - we have the names, the 11 categories (Free School Meals, Additional Learning Needs etc), and then the level the children attained in English Oracy, Reading, Writing, English Overall, welsh Oracy, Reading, Writing, Welsh Overall, Maths and Science. I would therefore love to be able to easily calculate how many in the class attained level 4, and also, how many FSM (or ALN etc) pupils attained level 4 and so on. Is this possible? I have done this with a pencil, paper and calcualtor for so many years, would love if there was an easier option!!
I realise that this is a big request - but here's hoping someone would point me in the right direction! Any help would be appreciated. Thanks!
MacBook Pro, Mac OS X (10.7.2)