Can't you apply the weight value to each assignment and then use those numbers to filter out the two smallest (lowest) ?
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
Assuming weighting is a 0 to 1 weighting and the sum of the weights = 1. Otherwise need to divide by sum(A2:A13)Code:=AVERAGE(IF((A2:A13)*(B2:B13)>=LARGE((A2:A13)*(B2:B13),COUNT(D2:D13)-2),(A2:A13)*(B2:B13),FALSE))
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.
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.
Bob, what if there aren't 8 because it's the start of the year?
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.
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.
Here is the data I was referring to in #8:
Bookmarks