Formula for grading

A

anonymous

New member
Joined
Sep 24, 2014
Messages
2
Reaction score
0
Points
0
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?
 
Can't you apply the weight value to each assignment and then use those numbers to filter out the two smallest (lowest) ?
 
Yeah but you want to remove the 2 lowest grades that having removed them will improve the grade the most.
 
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
 
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:
This array formula will average the top 8

=AVERAGE(LARGE(A2:A11*B2:B11,{1,2,3,4,5,6,7,8}))
 
Last edited:
Bob, what if there aren't 8 because it's the start of the year?
 
anonymous said:
Yeah but you want to remove the 2 lowest grades that having removed them will improve the grade the most.
Click to expand...

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:
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:
Example of Weights applied to Student Grades

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

Attachments

  • Weights.xlsx
    11.8 KB · Views: 17
WizzardOfOz said:
Check your formula on D13 = SUM not AVERAGE.
Click to expand...

Hi Wizard
My Formula on D13 is intentionally SUM not AVERAGE as I was taught to calculate a weighted average by summing the products of weight x each data point and dividing by the sum of weights (1 in this case).
This then gives a weighted average of 58.6032 for the values in Col B (excluding the lowest two). I can't understand why you would then want to divide by 8, arriving at a figure of 7.3254, but I'm open to
persuasion. :typing:
 
You must log in or register to reply here.
Back
Top