average without the six largest and smallest numbers

**Mengo85** · 04-10-2013, 09:49 AM

Hi all,

I have a range of values along row 37 from column K to KU. I would like to find the average without using the 6 largest and 6 smallest values.

I hope you can help me out.

Thanks in advance.

I am looking forward to hearingf rom you.

**benishiryo** · 04-10-2013, 10:03 AM

hi Mengo85, something like:
=AVERAGEIFS(K37:KU37,K37:KU37,">"&SMALL(K37:KU37,6),K37:KU37,"<"&LARGE(K37:KU37,6))

**Mengo85** · 04-10-2013, 10:25 AM

super, danke!

**daddylonglegs** · 04-10-2013, 10:41 AM

Note that you may exclude fewer than 6 smallest/largest values if you use AVERAGEIFS like that (if there are duplicate numbers around the cut-off points). It may be simpler to use TRIMMEAN which explicitly averages wihout the largest/smallest values, i.e.

=TRIMMEAN(K37:KU37,12/COUNT(K37:KU37))