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)

 

Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
description for autoplay blocking in settings page
HotCakeX in Discussions on
8 Replies