Averaging grades

**RandomName** · 09-02-2008, 02:28 PM

I'm a teacher. I use Excel to keep a record of my students' grades. Here's my question.

Let's say I have 20 grades for a student's assignments in one row. In another row I have the number of points possible on each assignment.

I want to average the student's assignment grades, making each assignment worth the same amount regardless of the number of points. I also want to drop the three lowest grades in percentage terms. So if the student made 9/10 on one assignment and 15/20 on another, I want to drop the 15/20 grade.

I have written a Visual Basic subroutine to do this, but it's clunky. There must be a better way.

Any suggestions on the simplest, most elegant way to accomplish this task?

Thanks in advance

**daddylonglegs** · 09-02-2008, 03:35 PM

When you say you have 20 grades I assume these are numbers, so if grades are in A1:A20 and possible points in B1:B20 then this formula will give you the average percentage, excluding the lowest 3

=SUM(LARGE(A1:A20/B1:B20,ROW(INDIRECT("1:"&COUNT(A1:A20)-3))))/(COUNT(A1:A20)-3)

confirmed with CTRL+SHIFT+ENTER

Note: assumes that B1:B20 will all have values but that some assignments may not be completed so A1:A20 could be blanks

Formula will give an error unless you have at least 4 grades

**Cheeky Charlie** · 09-02-2008, 05:35 PM

This is similar, and done in rows. I assumed data from B2 to U2 as scores and B3 to U3 as 'out of's.

I also figured If one would have to write /count(range)-'number to exclude' you might as well put 17, particularly as there's so much faffing if you want to change the range anyway...

=sumproduct(b2:u2/b3:u3,--(b2:u2/b3:u3>small(b2:u2/b3:u3,3)))/17

**shg** · 09-02-2008, 05:48 PM

Charlie,

What does your formula do if the 3rd and 4th lowest grades are the same? Or if all the grades are the same?

**Cheeky Charlie** · 09-03-2008, 10:54 AM

It doesn't work...

sulk

would it work with < large 17? Similar problems surely?

**shg** · 09-03-2008, 11:42 AM

sulk

would it work with < large 17? Similar problems surely?

No, because it pick the N largest values, which works fine with duplicates.

**daddylonglegs** · 09-03-2008, 03:27 PM

I suppose I might as well have suggested

=AVERAGE(LARGE(A1:A20/B1:B20,ROW(INDIRECT("1:"&COUNT(A1:A20)-3))))

confirmed with CTRL+SHIFT+ENTER

of course you could replace COUNT(A1:A20)-3 with 17 if you always have 20 numbers in that range, but using COUNT will allow you to calculate the average, without the 3 smallest, even if some assignments are incomplete