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?  Reply With Quote

2. Can't you apply the weight value to each assignment and then use those numbers to filter out the two smallest (lowest) ?  Reply With Quote

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

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  Reply With Quote

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  Reply With Quote

6. This array formula will average the top 8

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

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

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).  Reply With Quote

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.  Reply With Quote

10. ## Example of Weights applied to Student Grades

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

#### Posting Permissions

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