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

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


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

Reply
20 replies

May 22, 2012 1:47 PM in response to Gwyn Plem

You can add 5 footer rows (which is the max allowed in Numbers) to your table and summarize (like we did before) in the footer:


User uploaded file


In the footer rows I used column K to set the level to equal (or exceed) to be counted. So in the row or the footer with 1 in column K an entry in column M will only be counted if it is greater or equal to :


The formulas are:

M9=COUNTIF(M, ">="&$K9)

select M9 and fill down


then select M9 through M3 and fill to the right to get a summary for each column.


I don't know how to relate the "y" and "n" entries in columns B thru L to the numbers in columns M thru V. If you can explain that there is further automation possible.

May 22, 2012 2:02 PM in response to Wayne Contello

Thanks very much. The Y and N are True or False statments - maybe checkbox would be better? If a child has Free School Meals (FSM) - I have put a Y for yes. Would a checkbox make more sense I wonder? What I want of course then is the percentage of pupils who have free school meals (FSM) who had a level 3/4 or 5 etc. Then ALN, SP etc etc. I get confused even explaining it!! Thanks again for your valuable time.

May 22, 2012 2:10 PM in response to Gwyn Plem

to compute the % of students taking a free lunch you can enter the following formula in cell B9:


B9=COUNTIF(B, "=y")/COUNTA(A)


select cell B9 and format as a "%" by clicking the button in toolbar



To get the % pupils who are taking a free lunch AND have a rating >=3 is that in any of the columns after column M or in a specific one... please remember the headings mean absolutely nothing to me.

May 22, 2012 2:15 PM in response to Wayne Contello

Thanks - Sorry It's not the % of pupils who take free school meals, rather what % of the free school meals pupils achieved a level 3,4 or 5 and so on. For example I would like to figure out how many of the Additional Learning needs (ALN) pupils had a level 4 or 5 but not how many ALN there were or what % were ALN. I do appreciate your time, and my muddle explinations arent making things any the easier! Thanks again

May 22, 2012 6:13 PM in response to Gwyn Plem

Hi Gwyn,


Here's a layout that seems to work (click for larger image):

User uploaded file

The top table, "Data" is your original data table, the bottom, "Summary", gives the percentage of students in each category achieving a 4 or 5 level in each subject area. The bottom row (All) gives the overall percentage of students in All categories achieving these levels in each subject area.


Formulas:


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


Fill this formula down to the penultimate row (Eth) of column B (Eng O), then edit the formula in each row, using the following steps:


  1. In C3, enter "C"; in C4, enter "D" (without the quotes).
  2. Select both cells, then drag the Fill handle (small circle at the bottom right corner of the selection) down to continue the series of letters to C12, where the letter will be "L".
    These are temporary markers to aid in editing the formula. They will be erased when you fill the formula to the right (below).
    In each cell, starting at B3 you need to edit the two references to Data::$B, replacing both Bs with the letter in that row of column C. Do NOT make changes to the Data::M reference.
  3. Double click the lozenge containing the first reference to enter edit mode. Click at the right end of the reference, delete the B, then type the letter in that row of column C. Repeat with the third lozenge in the formula.
  4. Press return to move to the next row.
  5. Repeat steps 3 and 4 until you have edited the last formula (in row 12).

Formula 2:


Summary::B13 =COUNTIF(Data :: M,">=4")/COUNTA(Data :: $A)


After entering the formulas into column B, Select cells B2 - B13, then drag the Fill handle right to fill the formulas into columns C - K (Sci).


With all cells B2-K13 selected:


Open the Inspector, Choose the Cell Format inspector (42), then choose Percentage from the pop-up menu.

Set the number of decimal places to none (blank), or to a different value if a more precise display is desired.


Save.


Done.


Regards,

Barry

May 23, 2012 4:41 AM in response to Gwyn Plem

Hi Gwen,


Here are the expressions I used. Refer to the table names in my post.


Frequency of Membership table, B2:


=COUNTIF(Main :: B, $A)


Fill Down and Across.


Frequency of Attainment table, B2:


=INDEX(FREQUENCY(Main :: M,$A$2:$A$4),ROW()-1)


Fill Down and Across.


Correlation 3s table, B2


=COUNTIFS(Main :: B, "y", Main :: M, 3)


Correlation 3s table, B3:


=COUNTIFS(Main :: C, "y", Main :: M, 3)


etcetera...


Fill Across.


For the 4s and 5s, substitute 4, then 5 as here:


=COUNTIFS(Main :: B, "y", Main :: M, 4)


In the correlation tables I used Conditional Format to set the colors, one rule for each different frequency (count) from 0 to 5.


Regards,


Jerry

May 23, 2012 5:43 AM in response to Barry

This is working great. Just one question!! If I have no children in a certain category - for example in my 'true' list I have no Summer Born children - the formula throws a warning - a divisible by zero error probably. Is the only option to delete that category? I am thinking for future years - that I would like it to stand as is more than anything - but will take a empty category out if that is the easiest option!!

Thanks!

Gwyn

May 23, 2012 5:50 AM in response to Gwyn Plem

you can test for errors using the function iferror() which works like:


=iferror(<FUNC>, <RESULT WHEN ERROR>)


<FUNC> is the existing function which is giving you the warning

<RESULT WHEN ERROR> is what to return when there is an error from <FUNC>



e.g.:

if

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


is causing the problem 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

Calculating % of pupils in certain categories

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.