Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Level 2

164 points

Calculating % of pupils in certain categories

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

User uploaded file

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)

Posted on May 22, 2012 1:24 PM

20 replies

May 23, 2012 6:53 AM in response to Gwyn Plem

Gwyn,

Here's what I think I would use in your situation. From my first reading of your post I was thinking about presentation and Pareto charts are always my first choice, but it took me some time to get my arms around this one because you have so many dimensions in the data...

It's not fancy but it directs the eye to the higest performing program and the lowest performing program simply and effectively. A more robust set of data would show more gradation, but your proxy data suffices.

The Composite score is the sum of the skill scores for each treatment, so a score of 5 is worth more than a score of 4 and so forth. I derived the composite table from the individual score tables for 3s, 4s and 5s using the expression:

=SUM(LOOKUP(A, Correlation 3s :: $A,Correlation 3s :: $B), LOOKUP(A,Correlation 4s :: $A, Correlation 4s :: $B), LOOKUP(A, Correlation 5s :: $A,Correlation 5s :: $B))

The result is sortable, which make it easy to show the declining effect of the treatment as you move left to right on the chart.

Jerry

Barry

Level 9

59,861 points

May 23, 2012 10:57 AM in response to Wayne Contello

Just a comment:

"you could do this:

Summary::B2: =iferror(COUNTIFS(Data :: $B,"y",Data :: M,">=4")/COUNTIF(Data :: $B,"y"),"")

this will return nothing on error like div_by_0 errors but when there is no error return the proper result"

Not quite "nothing," actually. It's not possible to return "nothing" from a formula and leave its cell "empty." What theis formula does on receiving an error is return a null string, a text string with zero length. Numbers will recognize this as 'text' where appropriate. In a line graph, a text entry in the list of values being graphed will cause a gap in the line. A zero, on the other hand, will cause the line to dip to the zero line then rise to the next value—visually two very different graphs. In your case, and in most I can think of, the empty string ( "" ) is the best choice. An alternate might be "n/a" (not applicable).

Regards,

Barry

Gwyn Plem Author

Level 2

164 points

May 23, 2012 2:09 PM in response to Jerrold Green1

Dear Jerry,

Firstly thanks very much for your input. I have implemented almost all of your ideas - to great effect! The pareto chart has me stumped. I have created a new table and in A1 have entered the expression. It looks ok - and highlighted in the correlation tables are the programs (FSM, ALN etc) and Eng Oracy. But I get the the red triangle! I have double checked the expression - and it looks ok - here is the expression as I have it -

=SUM(LOOKUP(A, Correlation Level 3 :: $A,Correlation Level 3 :: $Eng Or),LOOKUP(A,Correlation Level 4 :: $A,Correlation Level 4 :: $Eng Or),LOOKUP(A,Correlation Level 5 :: $A,Correlation Level 5 :: $Eng Or))

As you can see I have my tables called slightly differently, but otherwise I cannot find a mistake! Was I suppose to enter it into a new table?

When working do I then carry on and change $B accordingly?

How do I chart this afterwards? I have no experience of Pareto chart - and cannot fathom how you chart one cell? Apologies - I am aware that you have spent a lot of time on this, but if you do have a few more minutes - I would appreiciate it. Thank you once again.

May 23, 2012 2:28 PM in response to Gwyn Plem

Gwyn,

When I get a chance, I'll streamline the expressions I used for the chart and try to do the entire operation without the interposing tables. Your table names may be too long, or not. I'll check it out and get back to you.

Jerry

Gwyn Plem Author

Level 2

164 points

May 23, 2012 2:52 PM in response to Jerrold Green1

Thank you very much.

Calculating % of pupils in certain categories