sum or similar with conditions

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

how do I do a SUM or similar but ignore the two highest and two lowest values?
there are 36 values in a column.
Thanks
 
Either

=SUM(A1:A10)-SUM(LARGE(A1:A10,{1,2}))-SUM(SMALL(A1:A10,{1,2}))

or

=SUM(LARGE(A1:A10,ROW(INDIRECT("A3:A"&COUNTA(A1:A10)-2))))

which is an array formula, so commit with Ctrl-Shift-Enter
 
=SUMPRODUCT(--(A1:A36<LARGE(A1:A36,2))*--(A1:A36>SMALL(A1:A36,2))*A1:A36)


how do I do a SUM or similar but ignore the two highest and two lowest
values?
there are 36 values in a column.
Thanks
 
=IF(COUNT(A2:A37)>4,SUM(A2:A37,-LARGE(A2:A37,{1,2}),-SMALL(A2:A37,{1,2})),"Insufficient
Data")
 
You don't need an * and the double unary, and the values don't need to be
coerced

=SUMPRODUCT(--(A1:A36<LARGE(A1:A36,2)),--(A1:A36>SMALL(A1:A36,2)),A1:A36)
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sum Values from different column 1
Trying to find additional ranges outside of min, max, and average 2
SUMIF SUBTOTAL OR SUMPRODUCT? 2
Can I have two conditions in a SUMIF? 5
MIN/MAX and Lookup 2
Conditional Formating: MAX & MIN 3
Using Lookup 1
I need my SMALL formula to ignore zero's 7

Back
Top