+ Reply to Thread
Results 1 to 11 of 11

Grading system for teachers

  1. #1
    Registered User
    Join Date
    03-30-2013
    Location
    Sweden
    MS-Off Ver
    Mac 2011
    Posts
    6

    Grading system for teachers

    Have a question if someone would be nice to help me:

    I am a teacher.

    Swedish grading system (secondary upper grade) uses the grades A-F.
    To get the grade A on a course (a course that has for example 5 criterias to live up to), the course's ALL 5 criterias have to be full filled up to level A. Only then is the final grade A.

    In a course that have 5 criterias to live up to I have a student who has lived up to let's say A, C, A, C and C on the 5 criterias.

    I want excel to calculate the final grade.

    The grading system works like this: to get an A in a course, ALL 5 criterias must have reached A.
    To get the grade B however, a MAJORITY of the criterias of A must have been reached (in this case 3 A's)
    The student must then of course also have full filled all E (5) and all C (5 of them as well).

    So if I in the first column in a spread sheet on the first row have the first criteria, second column I have the 2nd criteria (and so forth) and in row 2 puts A (for grade A) under first criteria, C (the grade C) under the 2nd criteria and so forth –*how do I then in the end column get excel to calculate how many A's the student have reached? And how many C's and E's?

    I would also like excel to give me a nice diagram that shows graphically (with bars or something like that) that for example this student (in the example above) has reached the final grade C (which is the total in this case as the student have met all criterias of E, all criterias of C but not a majority of the A criterias (which if he had would have resulted in the final grade B.
    Attached Files Attached Files

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Grading system for teachers

    Please see the attachment.

    I've used a user defined function to calculate the grades (See the Module1 tab in the VBA editor) and sparklines to show the graphs.

    The function code is reproduced below.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Martin

  3. #3
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,256

    Re: Grading system for teachers

    {=(CHAR(ROUNDUP(AVERAGE(CODE(C2:G2)),0)))}
    This array formula works also
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  4. #4
    Registered User
    Join Date
    03-30-2013
    Location
    Sweden
    MS-Off Ver
    Mac 2011
    Posts
    6

    Re: Grading system for teachers

    My god you are fast, thank you so very much...!
    I will surely contribute to Cancer Research UK if that is your wish.

    There are still some things I need to get right;

    1) If a student has even only one "F", the final grade has to be F. No matter if there are A's, E's or C's among what the student full filled.


    2) If the student (taking a coarse that has 5 criterias to fullfill) has for example 3 A's and 2 E's it means that he or she has not reached C on two criterias. To get final grade "B", ALL C's must have been full filled.
    So in this case the final grade has to be E. That is because to get "B" as final grade all C's must be full filled and a majority of A's. So, to be clearer: no matter how many A's you've got, if you have one E you have to lift that up (by studying more!) to a C to be able to get the B as a final grade.

    Is it possible to do calculations like that?

    In Apples iWork "Numbers" I did this (see attached pdf) - but that program is SLOW, thats why I wanna do this in excel.

    example.pdf

  5. #5
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Grading system for teachers

    Try this variant.

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    03-30-2013
    Location
    Sweden
    MS-Off Ver
    Mac 2011
    Posts
    6

    Re: Grading system for teachers

    Thanks!
    However I don't know where to put this function (not used to excel!). Is there any possibility for you to guide me to that or post a worksheet with the function already inserted in the right place?

  7. #7
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Grading system for teachers

    Open the previous attachment.
    Open the VBA editor by hitting Alt F11.
    Go to Module 1
    Paste in the function on top of the existing one
    Go back to the sheet by hitting Alt F11.

    Hit F9 to recalculate the workbook

    Remember to save the workbook as a macro enabled workbook .xlsm

  8. #8
    Registered User
    Join Date
    03-30-2013
    Location
    Sweden
    MS-Off Ver
    Mac 2011
    Posts
    6

    Re: Grading system for teachers

    Thanks!
    Ok, inserted the new code. Im on a mac (excel 2011) and dont know how to recalculate the workbook.

  9. #9
    Registered User
    Join Date
    03-30-2013
    Location
    Sweden
    MS-Off Ver
    Mac 2011
    Posts
    6

    Re: Grading system for teachers

    Got it (recalculated the workbook). But something is wrong with the code, if I enter F anywhere it says "Value fault" (or something like that, its in swedish). Should result in F.

    And 3 A's and 2 C's results in C (not B as it should). Maybe I wasnt sucessful in recalculating the workbook after all ( used the ribbon Formula and Recalculate.

    I should "over wright" all of the the old code, ah? Or should the old AND the new code you posted above be pasted in module 1?

  10. #10
    Registered User
    Join Date
    03-30-2013
    Location
    Sweden
    MS-Off Ver
    Mac 2011
    Posts
    6

    Re: Grading system for teachers

    TEST1A.xlsm

    I thank mrice for the great help! However if someone could help me further:


    Something is wrong with the code.
    If I enter F anywhere it says "Value fault" (or something like that, its in swedish). Should result in F.

    Further: 3 A's and 2 C's results in C (not B as it should). Maybe I wasnt sucessful in recalculating the workbook after all ( used the ribbon Formula and Recalculate.

    Can someone help me changing the code in module 1?

  11. #11
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Grading system for teachers

    Back again ....

    Try this change ...

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1