Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Calculating average letter grades in an excel Markbook

  1. #1
    Join Date
    2nd January 2011
    Posts
    3

    Calculating average letter grades in an excel Markbook

    Hi there,

    Firstly I have only a limited knowledge and understanding of Excel and so ask for your forgiveness in advance if this is a primitive query!!

    I am a teacher and wish to create an excel ‘Markbook’ and have searched high and low all over the net for the answer to my problem but as of yet can only find answers to very similar but different questions. If anyone can offer a solution or some advice I would be most grateful.

    Task
    I would like to record and display the marks for homework completed on a week by week basis and would like to see a running average grade as the term progresses.

    Difficult part is that we give grades as a two part result based on a letter grade for attainment (X,A,B,C,D) and a number grade based on effort put into the work (1,2,3,4,5). i.e X1 for great work that the pupil has put a lot of effort into and at the other end of the scale D5 for very poor work with little to no effort spent.

    Progress so far…
    I have put together a simple example of the eventual Markbook and will include it with this post.

    I currently have a column for some sample names and a table for 12 weeks worth of marks.

    At the end of this table I have an Attainment Average column and an Effort Average column.
    In the example I have worked out how to average the effort part of the grade so have highlighted the Effort Average box green & left these out bar the grades for ‘Andrew’ which are for example only.

    Problems
    Using the formula below I have managed to convert the letter grades for each week into a number, then average them, round up and convert back into a letter grade.

    =CHAR(ROUND(AVERAGE(CODE(D4),CODE(F4),CODE(H4),CODE(J4),CODE(L4),CODE(N4),CODE(P4),CODE(R4),CODE(T4),CODE(V4),CODE(X4),CODE(Z4)),0))

    Unfortunately the above formula only works if all the grades have been entered and this is not the case during the term time with weeks left, if a boy is absent and misses a homework or if a project is less than 12 weeks due to sports etc.

    Also our top grade (X as an equivalent to an A*) returns a random letter not the correct average.

    Lastly I am reluctant to insert any hidden columns or have referenced cells hidden away as I am looking to make this table a template to reproduce for the number of classes taught and this could make pasting new sets of names difficult in the future.

    Thanks very much again in advance if you can help I am willing to try anything!! (also sorry for the long post)
    Andrew

    Markbook_AJW.xls

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    8th September 2004
    Location
    Northampton, England
    Posts
    2,808

    Re: Calculating average letter grades in an excel Markbook

    Hi andiwhite,

    Welcome to the Ozgrid forum.

    Try this as a formula in cell AC4, and fill down.

    =INDEX({"X";"A";"B";"C";"D"},ROUND(SUMPRODUCT(--(D4:AA4>5),MATCH(D4:AA4,{"X","A","B","C","D",1,2,3,4,5,0},0))/SUM(--(ISTEXT(D4:AA4))),0),1)

    It's an array formula, and must be confirmed with Shift + Ctrl + Enter, instead of just Enter.
    Regards,
    Batman.

  3. #3
    Join Date
    2nd January 2011
    Posts
    3

    Re: Calculating average letter grades in an excel Markbook

    Fantastic!

    I really appreciate your time thank you so much.

    Works like a dream! I think I just about understand how - had to look a few parts up but pretty much there now.

    Dont suppose you know of any way to make this cell show blank if no grades have been added yet do you? Dont worry if not this is more than enough to keep me going for all my classes!

    Thanks so much for your speedy reply
    Andrew

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    8th September 2004
    Location
    Northampton, England
    Posts
    2,808

    Re: Calculating average letter grades in an excel Markbook

    Andrew,

    The first action that the formula will take will be to check if any data has been entered in the range. If not, it will return a blank cell, otherwise it will do the calculation. You still might get a 'division by zero' error if numeric grades have been entered, but no alpha equivalents; is that likely?

    =IF(COUNTA(D4:AA4)=0,"",INDEX({"X";"A";"B";"C";"D"},ROUND(SUMPRODUCT(--(D4:AA4>5),MATCH(D4:AA4,{"X","A","B","C","D",1,2,3,4,5,0},0))/SUM(--(ISTEXT(D4:AA4))),0),1))

    It's still an array formula, and must be confirmed accordingly.
    Regards,
    Batman.

  5. #5
    Join Date
    2nd January 2011
    Posts
    3

    Re: Calculating average letter grades in an excel Markbook

    Legend!

    Once again that works perfectly! No grades -> No Average, Any or all grades -> Average grade!

    Fantastic help I would say that I know quite a lot about the subject that I teach but have no idea about excel, its nice to learn some new things from an expert in the field!

    I take my hat off to you and am very appreciative for your help, this is a great forum!

    Thanks so much
    Andrew

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Weighted Average Of Grades
    By smidge182 in forum EXCEL HELP
    Replies: 5
    Last Post: January 4th, 2008, 13:26
  2. Convert Letter Grades To Numbers `
    By Pyrrhonic in forum EXCEL HELP
    Replies: 3
    Last Post: June 21st, 2007, 15:15
  3. Calculating Grades with Dynamic Lists of Students and Assignments
    By Stefan in forum Excel and/or Access Help
    Replies: 1
    Last Post: October 6th, 2004, 18:30
  4. VBA : Excel VBA: Calculate grades
    By Javy Dreamer in forum EXCEL HELP
    Replies: 3
    Last Post: February 12th, 2004, 02:38

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
  •  
porno