Statistical
Winsorized mean and percentile-cutoff trimmed mean: capping versus excluding values
Compare clamping values to percentile cutoffs with averaging only values inside those cutoffs, and distinguish this from rank-based TRIMMEAN.
State the cutoff rule before changing the data
For the fictional six-value sample [2,40,45,52,55,200], PERCENTILE.INC at 0.2 and 0.8 gives 40 and 55. Clamping to those cutoffs produces [40,40,45,52,55,55] with mean 47.83. Keeping only values within the inclusive interval gives [40,45,52,55] with mean 48.
This is percentile-value cutoff trimming, not a general replacement for Excel TRIMMEAN. TRIMMEAN excludes a symmetric count of ranked observations, rounding its total removal count down to an even integer. Ties and percentile interpolation can make the two methods retain different observations.
Tied cutoff values are all retained by AVERAGEIFS. Choose and document the rule before inspecting results; extreme values may be valid. Neither method guarantees an unbiased estimate, and changing the data can change both cutoffs. The unchanged result after changing only 200 to 10000 is specific to this sample.
All six raw values and both cutoffs must be numeric; low must not exceed high. Keep the percentile and clamping helper formulas intact. This example rounds displayed means to two decimals.
| Raw Sample (A) | Winsorized [40, 55] (B) | Included in Trimmed? | Summary Comparison |
|---|---|---|---|
| 2 (Bottom tail) | 40 (Clamped up to P20) | Dropped (< 40) | Raw Mean = 65.67 (Skewed!) |
| 40, 45 | 40, 45 | Kept | P20 Cutoff D2 = 40 |
| 52, 55 | 52, 55 | Kept | P80 Cutoff D3 = 55 |
| 200 (Top tail) | 55 (Clamped down to P80) | Dropped (> 55) | Winsorized = 47.83 | Trimmed = 48.00 |
Winsorized Mean across clamped helper column B2:B7
MIN($D$3, MAX($D$2, A2)) caps both lower and upper tails cleanly without nested IFs.
=IF(OR(COUNT(A2:A7)<>6,COUNT(D2:D3)<>2),"Invalid inputs",IF(D2>D3,"Invalid inputs",ROUND(AVERAGE(B2:B7), 2)))Mean of values inside the inclusive percentile interval
Values strictly outside the interval are excluded. When custom cutoffs retain no observations, the mean is undefined and the formula reports No retained values.
=IF(OR(COUNT(A2:A7)<>6,COUNT(D2:D3)<>2),"Invalid inputs",IF(D2>D3,"Invalid inputs",IF(COUNTIFS(A2:A7,">="&D2,A2:A7,"<="&D3)=0,"No retained values",ROUND(AVERAGEIFS(A2:A7, A2:A7, ">=" & D2, A2:A7, "<=" & D3), 2))))Try the example
Use the original values or change them to test your understanding.