Alternative TRIMMEAN

[Brennen81]

Hello,

I am looking to see if there is a way to (auto) formulate an average omitting the top x% and bottom x%, where each % are different. As opposed to TRIMMEAN which is the same for top and bottom.

For example. To omit the top 20% and bottom 10% I can do it like so. Data in rows 1-10

=(SUM(A1:A20)-SUM(LARGE(A1:A20,{1,2}))-SUM(SMALL(A1:A20,{1})))/(COUNT(A1:A20)-3)

So the top 2 (20%) and bottom 1 (10%) are omitted from the sum and then divided by the count less the 3 omitted.

Is there any way to rewrite this formula to be more on going and keeping the %'s? For example, If I added 10 more rows of data, the formula would automatically exclude the top 4 and bottom 2? Assuming I always had a range larger than needed.

Thank you so much for any replies and help that may come.

[sweep]

Hi,

does this averageifs help at all?

=AVERAGEIFS(A1:A100,A1:A100,"<"&80%*MAX(A1:A100),A1:A100,">"&10%*MAX(A1:A100))

[samba_ravi]

=AVERAGE(INDEX(LARGE(A1:A100,ROW(INDIRECT(FLOOR(COUNT(A1:A100)*20%,1)+1&":"&COUNT(A1:A100)-FLOOR(COUNT(A1:A100)*10%,1)))),0))
try this

[Ali Kırksekiz]

Please Login or Register  to view this content.

This in an array formula and so must be confirmed with CTRL+SHIFT+ENTER and not just ENTER

[Brennen81]

=AVERAGEIFS(A1:A100,A1:A100,"<"&80%*MAX(A1:A100),A1:A100,">"&10%*MAX(A1:A100))

When I use this formula it gives me an average of 4.5 on a range containing number 1-10. Using those numbers, the formula should average only number 2-8, omitting 1 for the bottome 10% and 9&10 for the top 20%. The average of 2-8 is 5.


=AVERAGE(INDEX(LARGE(A1:A100,ROW(INDIRECT(FLOOR(COUNT(A1:A100)*20%,1)+1&":"&COUNT(A1:A100)-FLOOR(COUNT(A1:A100)*10%,1)))),0))

This worked!


=AVERAGE(IF(A1:A10>PERCENTILE(A1:A10,0.1),IF(A1:A10<PERCENTILE(A1:A10,0.8),A1:A10)))

This worked only on a range from A1:A:10. When I increase the range the final number changes.