cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Using SUM, MIN, and COUNT in same formula

Doug Callejas
Copper Contributor

‎Mar 06 2018 11:58 PM - last edited on ‎Jul 25 2018 11:17 AM by

‎Mar 06 2018 11:58 PM - last edited on ‎Jul 25 2018 11:17 AM by

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 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

2,504 Views
0 Likes
1 Reply
Reply
1 Reply
Tobias Beer

‎Mar 07 2018 04:20 AM - edited ‎Mar 07 2018 04:26 AM

‎Mar 07 2018 04:20 AM - edited ‎Mar 07 2018 04:26 AM

Re: Using SUM, MIN, and COUNT in same formula

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)

 

0 Likes
Reply