Need a formula to find the minimum value in an array, then perform calculations on ....

Need a formula to find the minimum value in an array, then perform calculations on adjacent cells.

So what I'm really trying to do is create a grade book where the percentage is calculated by dropping the lowest score. Each assignment has a different amount of points possible. So I have three columns: score, possible, and percent in A, B, and C respectively. What I would like to do is find the lowest percent, then subtract that corresponding score from the sum of the scores, and subtract the corresponding possible from the sum of the possibles. Then divide to find the percentage. So something like:
=(sum([A:A])-[score next to lowest percent])/(sum(B:B)-[possible next to lowest percent])
I just don't know how to tell it to perform operations on cells next to another cell.
And in some cases I'll drop two or more scores so it would probably be best to use small(C:C,1) and small(C:C,2) and so on for the minimum.

Originally Posted by shg

Why not just =(sum(percents) - min(percents)) / (count(percents) - 1)

Because that would only be correct if every entry in possible is the same. For example

score possible percent
1 10 10
2 15 13.33
3 20 15

then the average percent is 12.78
but (1+2+3)/(10+15+20)=13.33

Originally Posted by TheExcelphile

I think you need to use a combination of Match and Index. Attachment 191667

Yupp that was it. Thanks!