求助筛选部分数据的球平均值

无铭剑客

如图，求B列的平均值。要求范围在0

edd9a7561b85ad81f863408a5954c805.png

edd9a7561b85ad81f863408a5954c805.png

求问.zip (9.15 KB, 下载次数: 174)

2021-12-22 11:59 上传

点击文件名下载附件
求问.zip

十分爱

=SUMPRODUCT(((B$2:B$74>0)*(B$2:B$74<0.2)+(B$2:B$74>=0.2)*(B$2:B$74<=0.4)*(A$2:A$74>10))*B$2:B$74)/SUMPRODUCT(((B$2:B$74>0)*(B$2:B$74<0.2)+(B$2:B$74>=0.2)*(B$2:B$74<=0.4)*(A$2:A$74>10)))

崔松岩

=(SUMIFS(B2:B74,B2:B74,"<0.2",B2:B74,">0")+SUMIFS(B2:B74,B2:B74,">=0.2",B2:B74,"<=0.4",A2:A74,">10"))/(SUMPRODUCT((B2:B74>0)*(B2:B74<0.2))+SUMPRODUCT((B2:B74>=0.2)*(B2:B74<=0.4)*(A2:A74>10)))