sum or similar with conditions

C

CHRIS K

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
 
B

Bob Phillips

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
 
K

kk

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

Aladin Akyurek

=IF(COUNT(A2:A37)>4,SUM(A2:A37,-LARGE(A2:A37,{1,2}),-SMALL(A2:A37,{1,2})),"Insufficient
Data")
 
B

Bob Phillips

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