如何排除极大值计算多数数据平均值？含视频数据场景疑问

Got it, let's fix this problem properly. The core issue here is that your current formula uses a rigid rule (like excluding a fixed number of top values) which leads to unintended exclusions when all video view percentages are normally distributed. The solution is to dynamically identify actual extreme outliers first, then only exclude them if they exist—otherwise calculate the average using all your data.

Recommended Method: IQR (Interquartile Range) #

This approach is robust against extreme values and works great for non-normal datasets (like view percentage distributions). Here's how to implement it step by step:

Step 1: Calculate Key Statistics #

First, compute these values for your full dataset of view percentages:

• Q1: 25th percentile (lower quartile, the value where 25% of data falls below it)
• Q3: 75th percentile (upper quartile, the value where 75% of data falls below it)
• IQR: Q3 - Q1 (the range containing the middle 50% of your data)

Step 2: Define Outlier Threshold #

Set an upper bound for non-outlier values. The standard threshold is:
Upper Bound = Q3 + 1.5 * IQR
Any value above this bound counts as an extreme outlier. For stricter detection (only exclude very extreme values), use 3 * IQR instead of 1.5 * IQR.

Step 3: Conditional Average Logic #

• Count how many values exceed the upper bound.
  • If the count > 0: Calculate the average of values ≤ Upper Bound
  • If the count = 0: Calculate the average of all values (no exclusions needed)

Example Implementation (Excel) #

Assuming your view percentages are in column A, here's a nested formula that automates this logic:

=IF(COUNTIF(A:A,">"&QUARTILE.INC(A:A,3)+1.5*(QUARTILE.INC(A:A,3)-QUARTILE.INC(A:A,1)))>0, 
    AVERAGEIF(A:A,"<="&QUARTILE.INC(A:A,3)+1.5*(QUARTILE.INC(A:A,3)-QUARTILE.INC(A:A,1))), 
    AVERAGE(A:A))

How This Works for Your Sample Data #

For your given values (74.474%, 8.7927%, plus other videos):

• The 74.474% will far exceed the calculated upper bound, so it gets excluded automatically.
• If all your view percentages are clustered closely (no true outliers), the formula skips exclusion entirely—no more false removals of 4 videos.

Alternative: Z-Score Method #

If your data follows a normal distribution, you can use Z-scores to detect outliers:

• Calculate the mean (μ) and standard deviation (σ) of your dataset.
• A value is an outlier if its Z-score ((value - μ)/σ) is greater than 3 (adjust the threshold based on your needs).
• Apply the same conditional logic: exclude outliers only if they exist.

Key Takeaway #

Swap your fixed exclusion rule for a statistically grounded, dynamic outlier detection system. This ensures you only remove extreme values when they actually exist, while preserving all data in normal, evenly distributed scenarios.

内容的提问来源于stack exchange，提问作者kostya572