Previous 1 2 Next 20 Replies Latest reply: May 23, 2012 2:52 PM by Gwyn Plem
Gwyn Plem Level 2 (160 points)


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 -


Eng O
Eng R
Eng W
Wel OWel R
Wel W
D Duckyynnynynyyy4443433544
M Mouseynnynynnnny4334454435


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)
  • Wayne Contello Level 6 (17,554 points)

    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:


    Screen Shot 2012-05-22 at 3.43.13 PM.png


    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.

  • Gwyn Plem Level 2 (160 points)

    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.

  • Wayne Contello Level 6 (17,554 points)

    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.

  • Gwyn Plem Level 2 (160 points)

    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

  • Wayne Contello Level 6 (17,554 points)

    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


    had a level 4 or 5 in what?  Any of the categories in columns M thru V?  select ones? each column separately?

  • Gwyn Plem Level 2 (160 points)

    In each of the areas of learning! So I would like to know how many FSM or ALN or SP pupils had a level 4 in Maths and English etc - all areas! So complicated I just coudnlt figure out the layout! Thanks

  • Wayne Contello Level 6 (17,554 points)

    Screen Shot 2012-05-22 at 4.51.41 PM.png


    B9=COUNTIFS(B, "=y", $P, ">=4", $U, ">=4")/COUNTA($A)


    Does this do what you want?

  • Gwyn Plem Level 2 (160 points)

    Thanks - Yes I think that is it! Thank you so much!

  • Jerrold Green1 Level 7 (29,960 points)



    Here are some other views of the data that might be of interest.


    Screen Shot 2012-05-22 at 7.52.43 pm.png

    Screen Shot 2012-05-22 at 7.52.57 pm.png



  • Barry Level 7 (29,976 points)

    Hi Gwyn,


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

    Picture 6.png

    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.




    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.








  • Gwyn Plem Level 2 (160 points)

    This looks outstanding! Could you please tell me how you created this? Thank you so much for taking the time. It just looks great.

  • Gwyn Plem Level 2 (160 points)

    This is just incredible. I like this a lot. I am implementing this now, but it looks like a great solution. I will try and incorportate the great visual charts from Jerrold's answer if I can. Thank you so much. Blown my mind at what can be done! If only I can do it now! Thanks

  • Jerrold Green1 Level 7 (29,960 points)

    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)




    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.





  • Gwyn Plem Level 2 (160 points)

    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!!



Previous 1 2 Next