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:
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:
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.
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.
to compute the % of students taking a free lunch you can enter the following formula in cell B9:
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.
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
Here's a layout that seems to work (click for larger image):
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:
- In C3, enter "C"; in C4, enter "D" (without the quotes).
- 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.
- 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.
- Press return to move to the next row.
- Repeat steps 3 and 4 until you have edited the last formula (in row 12).
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.
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)
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.
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!!