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

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:A1 0)-2))))

which is an array formula, so commit with Ctrl-Shift-Enter

--
HTH

Bob Phillips

"CHRIS K" wrote in message
...
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
--
CHRISK

=SUMPRODUCT(--(A1:A36<LARGE(A1:A36,2))*--(A1:A36SMALL(A1:A36,2))*A1:A36)


"CHRIS K" wrote in message
...
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
--
CHRISK

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

CHRIS K wrote:
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

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:A36SMALL(A1:A36,2)),A1:A36)


--
HTH

Bob Phillips

"kk" <kkchoh @ yahoo dot com wrote in message
...
=SUMPRODUCT(--(A1:A36<LARGE(A1:A36,2))*--(A1:A36SMALL(A1:A36,2))*A1:A36)


"CHRIS K" wrote in message
...
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
--
CHRISK