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. Can't you apply the weight value to each assignment and then use those numbers to filter out the two smallest (lowest) ?

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

4. 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. 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

6. This array formula will average the top 8

=AVERAGE(LARGE(A2:A11*B2:B11,{1,2,3,4,5,6,7,8}))

7. Bob, what if there aren't 8 because it's the start of the year?

8. Originally Posted by anonymous
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).

9. 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.

10. ## Example of Weights applied to Student Grades

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

Page 1 of 2 1 2 Last

#### Posting Permissions

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