Drop lowest--wait! it's more complicated

[andreamcn]

This is for a gradebook...there are 10 homework assignments and I need a formula that will drop the lowest grade before finding the final proportion based on total points. Easy, right? But wait, the assignments are not all worth the same number of points. So I need to drop the lowest based on percent and then go back and calculate a grade based on total points. For example,

Joe 18 13 14 Total Pts 21 14 16 <tbody> </tbody>

<tbody>
</tbody>

I need to calculate the proportion for each assignment (e.g. 18/21 = .86; 13/14 = .93; and 14/16 = .88), recognize that the low score is the 18, then calculate a final total points proportion without the low score (i.e. (13+14)/(14+16) = .90).

My first instinct was to break this into parts. First I set up a second table where each grade is converted to a percent. Then I can easily identify the lowest proportion. But now I need to use that cell as a reference to go back to the original table and drop that grade, and I don't know how to do that. Does this seem like the right approach and if so how do I figure that out?

Thanks!

 

[RonB1111]

Assume your sample data starts in A1. Add a 3rd row for the % and enter this formula in B3 and copy across:

=B1/B2

Now this formula will give you the answer you want:

=SUM(B1:D1)-INDIRECT(ADDRESS(1,MATCH(MIN(B3:D3),B3:D3,0)+1))

 

[konew1]

Here is a way that does not need the percentage calculation for every assignment for every student. On testing it I wonder if you have a policy for a student who achieves the same low score on 2 or more assignments.

Excel Workbook
	A	B	C	D	E	F
1	Joe	18	13	14	0.9000
2	ann	17	12	12	0.8286
3	other	20	14	13	0.9714
4	problm cld	18	7	8	0.8571	Two assignments scored 50% and both eliminated
5
6	Total Pts	21	14	16
7
8	PROOFING
9	Joe	0.857143	0.928571	0.875	0.90
10	ann	0.809524	0.857143	0.75	0.83
11	other	0.952381	1	0.8125	0.9714
12	problm cld	0.857143	0.5	0.5	0.71
Sheet3

 

[andreamcn]

Thank you!! I'll have to confer with my fellow instructors to decide how to deal with multiple low scores.