Forum Discussion
Using SUM, MIN, and COUNT in same formula
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...
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)