1 2 Previous Next 16 Replies Latest reply: Feb 8, 2014 5:34 AM by Wayne Contello
KippMajors78 Level 1 Level 1 (0 points)

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
  • 1. Re: Numbers for simple school data
    SGIII Level 5 Level 5 (4,130 points)

    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

  • 2. Re: Numbers for simple school data
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    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

  • 3. Re: Numbers for simple school data
    KippMajors78 Level 1 Level 1 (0 points)

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

     

    Screen Shot 2014-02-07 at 19.27.47.png
  • 4. Re: Numbers for simple school data
    Wayne Contello Level 6 Level 6 (13,620 points)

    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.

  • 5. Re: Numbers for simple school data
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    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

  • 6. Re: Numbers for simple school data
    SGIII Level 5 Level 5 (4,130 points)

    Is something like this what you are looking for?

     

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

     

    SG

  • 7. Re: Numbers for simple school data
    KippMajors78 Level 1 Level 1 (0 points)

    yes that pretty much it!!

     

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

  • 8. Re: Numbers for simple school data
    SGIII Level 5 Level 5 (4,130 points)

    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

  • 9. Re: Numbers for simple school data
    KippMajors78 Level 1 Level 1 (0 points)

    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

  • 10. Re: Numbers for simple school data
    SGIII Level 5 Level 5 (4,130 points)

    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

  • 11. Re: Numbers for simple school data
    Barry Level 7 Level 7 (29,180 points)

    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

  • 12. Re: Numbers for simple school data
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    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

  • 13. Re: Numbers for simple school data
    SGIII Level 5 Level 5 (4,130 points)

    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

  • 14. Re: Numbers for simple school data
    KippMajors78 Level 1 Level 1 (0 points)

    Thanks for the great tips Barry, I have read the Numbers Help page that you suggested and managed to add a conditional highlight to the cell I wanted.

     

    But is there a way to apply this format to many cells at the same time or must I create it for each individual cell?

1 2 Previous Next