MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Summing less highest and lowest value


Posted by Greg Galvan on August 08, 2001 2:09 PM

Is there anyway in Excel to Sum a list of numbers excluding the highest and lowest value?

Thank you for your time


Posted by Mark W. on August 08, 2001 2:23 PM

Are their duplicate values?

Posted by Aladin Akyurek on August 08, 2001 2:24 PM

Assuming the numbers of interest to be in A1:A5, use either of:

(1) =SUMPRODUCT((A1:A5<>MIN(A1:A5))*(A1:A5<>MAX(A1:A5))*A1:A5)

(2) =SUM((A1:A5<>MIN(A1:A5))*(A1:A5<>MAX(A1:A5))*A1:A5) [ This one must be array-entered, that is, you need to hit CONTROL+SHIFT+ENTER (not just ENTER) to enter it. ]

Aladin

Posted by Greg Galvan on August 08, 2001 2:28 PM


Yes, there can be

Posted by Greg Galvan on August 08, 2001 2:29 PM

Posted by Mark W. on August 08, 2001 2:35 PM

Bingo!

Posted by Mark W. on August 08, 2001 2:36 PM

If there were no duplicates...

=SUM(A1:A5)-MAX(A1:A5)-MIN(A1:A5)

...would have sufficed!