Formula to exclude outliers and calculate the average, min and max

[The_Snook]

Morning all,

I'm trying to calculate the average, min and max we have paid for a range of services. The data contains some outliers though which I would like to exclude from the calculation. My initial thought is to trim the top and bottom 10% (% may change?) and see how that looks. I've attached some sample data and the output I would expect to see.

NB - The actual data is mixed and hence will need to incorporate the service name into the calculation, my sample data is grouped for simplicity.

Thanks in advance,

Snook

[Bo_Ry]

Please try at
E4 better use TRIMMEAN

=TRIMMEAN(IF($A$2:$A$31=$D4,$B$2:$B$31),20%)



=AVERAGEIFS($B$2:$B$31,$A$2:$A$31,$D4,$B$2:$B$31,">"&PERCENTILE.INC(IF($A$2:$A$31=$D4,$B$2:$B$31),10%),$B$2:$B$31,"<"&PERCENTILE.INC(IF($A$2:$A$31=$D4,$B$2:$B$31),90%))


F4 MS365
=MINIFS($B$2:$B$31,$A$2:$A$31,$D4,$B$2:$B$31,">"&PERCENTILE.INC(IF($A$2:$A$31=$D4,$B$2:$B$31),10%))

F4 other Version
=AGGREGATE(15,6,$B$2:$B$31/($B$2:$B$31>PERCENTILE.INC(IF($A$2:$A$31=$D4,$B$2:$B$31),10%))/($A$2:$A$31=$D4),1)

G4 MS365
=MAXIFS($B$2:$B$31,$A$2:$A$31,$D4,$B$2:$B$31,"<"&PERCENTILE.INC(IF($A$2:$A$31=$D4,$B$2:$B$31),90%))

G4 other Version
=AGGREGATE(14,6,$B$2:$B$31/($B$2:$B$31<PERCENTILE.INC(IF($A$2:$A$31=$D4,$B$2:$B$31),90%))/($A$2:$A$31=$D4),1)

[The_Snook]

Cheers Bo_Ry, that's exactly what I was after. The rep police won't allow me to credit you but I'll do it once it's enabled.

NB (for others) - These solutions are array formulas.

Snook