how to drop grades

[excelhelp12]

This is what I have so far:
column E is for Quizzes and column I is for Labs.
Quizzes (col E) counts for 10%.
Labs (col I) counts for 20%.

I want to drop the lowest 4 grades out of all the labs and quizzes for each student. For example, if the lowest 4 are all quizzes then no labs get dropped.

Therefore to compute how much each grade is worth I need to divide the percent by the number of undropped grades in each column.

MY QUESTION IS: how do you make this formula ?

 

[iliace]

It would probably depend on where the students are. Here's one way:

Excel Workbook
	E	I	J	K	L
1	Quiz	Lab
2	100	98
3	100	96		Quiz Avg	Lab Avg
4	99	95		88.46667	87.13333
5	98	91
6	92	91
7	91	91
8	91	90
9	87	89
10	86	89
11	86	87
12	85	85
13	79	82
14	78	77
15	78	76
16	77	70
17	73	69
18	67	68
19	66	62
20	64	60
Sheet1