Average a dataset after discarding the most extreme high and low values.
Copy this formula
=TRIMMEAN(A2:A100, 0.1)TRIMMEAN removes a percentage of data points from each end before averaging — 0.1 drops the top and bottom 5% combined. It's a robust average for data with a few extreme outliers.
=TRIMMEAN(Scores, 0.2)→74Average after trimming the extreme 20%.
=TRIMMEAN({1,50,52,54,56,58,60,62,64,500}, 0.2)→57With ten values, 20% removes the lowest and the highest value before averaging.
=AVERAGE({1,50,52,54,56,58,60,62,64,500})→95.7The ordinary average of the same values is pulled up by the extreme 500.
TRIMMEAN splits the percent across both ends, so 0.1 removes 5% from the top and 5% from the bottom.
It multiplies the count by the percentage and rounds down to an even number. With 10 values and 0.25, it removes 2 values, not 2.5.
MEDIAN returns only the middle value. TRIMMEAN averages everything that is left after removing the extremes, so it uses more of the data.
Use AVERAGEIFS with a threshold: =AVERAGEIFS(A2:A100, A2:A100, "<=1000").
Need a formula for your own data?
Describe what you want in plain language and let ExcelGPT write, explain, and verify the formula — free to start, no plugin.