Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: Formula for grading

  1. #1

    Formula for grading



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I've got a worksheet here that needs to calculate a student's average but it needs to take into account the fact that the 2 lowest grades are going to be dropped. There's a total of 10 assignments, each with a different "weight". I've calculated the percentage(letter grade) of each assignment and the weight of each assignment. So I'm not sure if I need some sort of logic statement to sift through the data or what. It's not as simple as using MIN or SMALL to find the lowest numbers because those functions don't take into account each individual weight. Help?

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    2010
    Can't you apply the weight value to each assignment and then use those numbers to filter out the two smallest (lowest) ?

  3. #3
    Yeah but you want to remove the 2 lowest grades that having removed them will improve the grade the most.

  4. #4
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    Sorry can only get you half the way. If you multiply weight x score into col B in this example
    then average removing lowest two = AVERAGEIF(B:B,">="&LARGE(B:B,COUNT(B:B)-2)) (array formula Press Cntrl+Shift+Enter)

    But trying to change B:B to score x weight is not working

  5. #5
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    Formula is
    Code:
    =AVERAGE(IF((A2:A13)*(B2:B13)>=LARGE((A2:A13)*(B2:B13),COUNT(D2:D13)-2),(A2:A13)*(B2:B13),FALSE))
    Assuming weighting is a 0 to 1 weighting and the sum of the weights = 1. Otherwise need to divide by sum(A2:A13)

    LARGE orders the data and gets the N - 2 lowest
    IF gets an array of values either the product (A x B) or FALSE (not averaged)

    Array formula so need Cntl+Shift+Enter not Enter
    Last edited by WizzardOfOz; 2014-09-25 at 06:28 AM.

  6. #6
    This array formula will average the top 8

    =AVERAGE(LARGE(A2:A11*B2:B11,{1,2,3,4,5,6,7,8}))
    Last edited by Bob Phillips; 2014-09-25 at 02:43 PM.

  7. #7
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    Bob, what if there aren't 8 because it's the start of the year?

  8. #8
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by anonymous View Post
    Yeah but you want to remove the 2 lowest grades that having removed them will improve the grade the most.
    I might have this wrong, put Ive made a list of percentage marks for 10 assignments with a list of weights for each (adding to 1) in A2:B11. I get a different result because I believe that by multiplying A *B the average is being calculated, and its only necessary to discard the smallest two values in Col C and sum the rest giving a WA of 58.6032% (Almost a B) This looks reasonable compared with the list in B:B, whereas the formula results give only 7.3254% (Ungraded).
    Last edited by Hercules1946; 2014-09-26 at 10:26 PM.

  9. #9
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    2010
    Here is my example:

    I cant post the details Im afraid, because HTML maker won't convert the code when I try to post it. I dont know why.
    Last edited by Hercules1946; 2014-09-26 at 10:33 PM.

  10. #10
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    2010

    Example of Weights applied to Student Grades

    Here is the data I was referring to in #8:
    Attached Files Attached Files

Page 1 of 2 1 2 LastLast

Posting Permissions

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