Skip navigation

Numbers for simple school data

310 Views 16 Replies Latest reply: Feb 8, 2014 5:34 AM by Wayne Contello RSS
1 2 Previous Next
KippMajors78 Calculating status...
Currently Being Moderated
Feb 7, 2014 11:33 AM

Hey,

 

I'm completly new to speadsheets and have beem asked to compare my subject (I'm a teacher) to another's results.

I just want to be able to get the:

 

- percentages of A-Bs and A-Cs etc per the two classes

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

 

I am using numbers 3.1 on my Mac. I'm sure there is most likley a very simple way to do this, but I am very unfamilar in the world of spreadsheets!

 

Can any offer any much-appreciated help?

iMac, iOS 6.1.2
  • SGIII Level 4 Level 4 (3,300 points)
    Currently Being Moderated
    Feb 7, 2014 11:52 AM (in response to KippMajors78)

    To get you started.... in addition to looking (via your menu) at Help > Numbers help to get a general feel for how things work, you should have a look at Grade Book and GPA under Education templates (File > New and look in the template chooser):

     

    Screen Shot 2014-02-07 at 2.49.12 PM.png

     

     

    SG

  • Jerrold Green1 Level 7 Level 7 (28,215 points)
    Currently Being Moderated
    Feb 7, 2014 12:10 PM (in response to KippMajors78)

    Hi Kipp,

     

    Welcome to the Numbers Discussions. I gather that you were intending to post a screen shot to show us your data, but if so it didn't make it. Shift-Command-4 to activate the screen grab selection tool, and after you've made the capture, click on the camera icon and follow the instrucitons in the dialog box. Your screen shot graphic will be on your Desktop.

     

    Sounds like you want to keep it simple for this first episode with spreadsheets, so that's what we will try to do for you.

     

    Jerry

  • Wayne Contello Level 6 Level 6 (12,670 points)
    Currently Being Moderated
    Feb 7, 2014 12:43 PM (in response to KippMajors78)

    Kipp,

     

    I still do not see enough context to undestand what is compared to what.  (GCSE) and Media Mock Grade do not mean anything to me.  Perhaps you can describe a little more what you want to do.

  • Jerrold Green1 Level 7 Level 7 (28,215 points)
    Currently Being Moderated
    Feb 7, 2014 12:48 PM (in response to KippMajors78)

    KippMajors78 wrote:

     

    I did indeed - sorry. VERY new I should ADD!

     

    Screen Shot 2014-02-07 at 19.27.47.png

    OK, got some data. Now what would you like the outcome to look like?

     

    Jerry

  • SGIII Level 4 Level 4 (3,300 points)
    Currently Being Moderated
    Feb 7, 2014 1:00 PM (in response to KippMajors78)

    Is something like this what you are looking for?

     

    Screen Shot 2014-02-07 at 3.59.33 PM.png

     

    SG

  • SGIII Level 4 Level 4 (3,300 points)
    Currently Being Moderated
    Feb 7, 2014 1:11 PM (in response to KippMajors78)

    Ok, here are some ideas.

     

    The Data table is just data, no formulas.

     

    The Count table counts the occurrences of each grade.

     

    You list the possible grades in column A.

     

    Then in B2, you try a formula like this:

     

       =COUNTIF(Data::A,$A2)

     

    In the formula editor it will look like this:

     

    Screen Shot 2014-02-07 at 4.09.33 PM.png

     

    If it gives you the result you expect, then copy it down and right in that table.

     

    Add a Footer Row at the bottom.

     

    In the cells there put =SUM(B) and =SUM(C)

     

    Then set up a Percentage table:

     

    The formula in B2 there would be

        =Count::B2÷Count::B$9

     

    Copy that to the other body cells in that table.

     

    Make sure the shaded rows are defined as Header row.

     

    SG

  • SGIII Level 4 Level 4 (3,300 points)
    Currently Being Moderated
    Feb 7, 2014 1:36 PM (in response to KippMajors78)

    then getting a percentage for say the 1st 2 rows (A-B), then 1st, 2md 3rd rows (A-C)

     

    A simple way to do this might look like this:

     

    Screen Shot 2014-02-07 at 4.26.29 PM.png

     

    I just set up an additional 'Count Consol' table, and put in the formula in cell B2:

     

         =SUM(Count::B2:B3)

     

    Then copied it to B3.

     

    And in C3 I put

     

      =SUM(Count::B2:B4)

     

    Then copied it to C3.

     

    And in the 'Percentage Consol' table I put in B2:

     

       =Count Consol::B2÷Count::B$9

     

    And copied it right and down.

     

    I don't type all that in. Type = and the formula editor appears, enter the function and where you need a range of cells you can select the range with your mouse (or trackpad) and Numbers will enter the range for you, including the table name.

     

    This is just one way to do this. There are other more elegant ways. But it should give you an idea of how you can approach the problem in Numbers.

     

    SG

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    Feb 7, 2014 1:39 PM (in response to KippMajors78)

    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:

    Screen Shot 2014-02-07 at 1.21.03 PM.png

    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

  • Jerrold Green1 Level 7 Level 7 (28,215 points)
    Currently Being Moderated
    Feb 7, 2014 1:39 PM (in response to KippMajors78)

    Kipp,

     

    You may find that graphical comparisons are helpful. Here are some examples for the set of grades on the right of your post...

    Screen Shot 2014-02-07 at 4.37.08 pm.png

    Jerry

  • SGIII Level 4 Level 4 (3,300 points)
    Currently Being Moderated
    Feb 7, 2014 1:46 PM (in response to KippMajors78)

    KippMajors78 wrote:

     

    Thanks Sg - looks brillaint. But some reason I can't get 'data' in my formula editor. What am i doing worng?

     

    Screen Shot 2014-02-07 at 21.18.23.png

     

    The Data is the name of the table I have in my example. Numbers will fill it in the table name for you if you click on that column.  You enter a table name by checking 'Table Name' in the Table inspector at right, then editing the name above the table.

     

    Screen Shot 2014-02-07 at 4.43.31 PM.png

     

     

    SG

1 2 Previous Next

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.