Omitting the top or bottom x values

[Brennen81]

Hello. I am looking to change the formulas below omit more than just the highest or lowest value.

I use these functions to find the average of the positive and negative numbers, while omitting the top or bottom value.
(Positive) =(SUMIF(L48:L67,">0")-MAX(L48:L67))/(COUNTIF(L48:L67,">0")-1)
(Negative) =(SUMIF(L48:L67,"<0")-MIN(L48:L67))/(COUNTIF(L48:L67,"<0")-1)

I am looking on how I can have it omit the top/bottom 2 values? In this case there are 20 different values in the array, both positive and negative mix.

thanks!!!!
Brennen

[FDibbins]

Try this...
=SUMIFS($A$1:$A$10,$A$1:$A$10,">"&SMALL($A$1:$A$10,2),$A$1:$A$10,"<"&LARGE($A$1:$A$10,2))

	A
1	1
2	2
3	3
4	4
5	5
6	6
7	7
8	8
9	9
10	10

gives answer of 33

Edit: to make this moere flexible, you could put the 2 (bolded) in its own cell, so you can chage from 2 highest 3 highest, 1 highest etc

[Brennen81]

Hello. I appreciate the reply. I think I am missing something. Does this omit the 2 positive or negative? I am looking for separate formulas to do both. I put that in my spreadsheet and it doesn't seem to work.

I attached an example of my data. On the first green cell below the number set I am looking to average the positive numbers without the top 2. Then the negative numbers without the bottom 2 on the next cell below that.

Could you help as to how you would put the formula in this sheet?

Average test.xlsx

[martindwilson]

is this an average? if so try trimmean() ignore this it is sooo wrong!

[Brennen81]

I got it. I did it this way. To average the positive numbers less the top 2...

=SUMIF((EB2570:EB2589,">0")-SUM(LARGE(EB2570:EB2589,{1,2})))/(COUNTIF(EB2570:EB2589,">0")-2)

This sums the numbers greater than 0, subtracts the sum of the largest 2, divides that number by the amount of numbers positive minus 2.

Then this for the negative.

=SUMIF((EB2570:EB2589,">0")-SUM(SMALL(EB2570:EB2589,{1,2})))/(COUNTIF(EB2570:EB2589,"<0")-2)


This make sense to all?


I need each of the positive and negative averages separatly so TRIMMEAN doesnt work in this case.