Hi Kipp,
You wrote:
- percentages of A-Bs and A-Cs etc per the two classes
For this, you'll need to count the number of each letter grade, then divide the sum of the counts for grades in the range you want to compare by the sum of the counts for all of the grades. The result will be a decimal fraction. Format the cell to display as percentage. In the example below, these results are shown in Table 2.
- if possible, highlight where my subject was higher/lower than the other results for that student (I've removed the names column for purpose of showing my data)
This can be done using conditional formatting to compare the cell containing your student's grade with the cell containing the other teacher's grade for the same student. Done most conveniently if you have the grades in adjacent columns. In the example below, this is shown on Table 1.
Example:
Table 1 (Left):
All data shown has been randomly generated for the example. In your case, this data would be pasted in from your grade book and from your colleague's.
The conditional format rules for cells in column B are shown for cell B15 (selected). The other rule sets are the same, but the two cell references shown are made to the cell in the same row of column C as the row of the cell to be formatted. See Numbers Help for details on how to do this.
Table 2:
There are three versions of essentially the same formula here.
B2, filled right then down to C6: =COUNTIF(Table 1 :: B,$A2)/COUNTA(Table 1 :: B)
B8, filled right to C8:
=(COUNTIF(Table 1 :: B,$A2)+COUNTIF(Table 1 :: B,$A3))/COUNTA(Table 1 :: B)
B10, filled right to C10:
=(COUNTIF(Table 1 :: B,$A2)+COUNTIF(Table 1 :: B,$A3)+COUNTIF(Table 1 :: B,$A4))/COUNTA(Table 1 :: B)
Each instance of COUNTIF counts the items in column B of Table 1 that match the grade listed in the specified cell of Table 2.
COUNTA counts all on the text entries in column B of Table 1.
Example is constructed in Numbers '09. Appearance will differ slightly in Numbers 3.
Regards,
Barry