Home

Using SUM, MIN, and COUNT in same formula

Doug Callejas
Occasional Visitor

I am trying to find the average of a students quiz grade by adding all 7 quizzes but also dropping their lowest quiz grade. For example, I did =SUM(B8:H8)-MIN(B8:H8) which added up to 565. Then using COUNT, I have to divide 565 by the number assignments minus 1. How would I be able to type that in a formula to find the average of their quiz grade using SUM, MIN, and COUNT?

 

B8:H8 = 100,95,85,100,95,90,85

1 Reply

I'm sure you can also do matrix formula voodoo, but...

 

FWIW, you can simply use helper columns to identify the relevant rows and respective values, then sum them up and calc the overall average from that.

 

Otherwise, you may need at least one helper column to identify the row with the min score and then only use SUMIF and COUNTIF on all other rows.

 

To determine the row with the min value, you can do sth like:

 

=IF(ROW(A1)=MATCH(MIN($A$1:$A$20);$A$1:$A$20;0);1;0)