在excel中使用标准偏差函数来满足特定条件? [英] Using standard deviation function in excel for a specific criteria?

问题描述

我目前正在做一个项目，想知道是否有办法计算数据集中指定范围内值的标准偏差.例如，我想计算我的整体数据中小于或等于 0.5 但大于或等于 -0.5 的数据点的标准差.

I am currently working on a project and was wondering if there is a way to calculate the standard deviation of values in a specified range in a data set. For example, I would like to calculate the standard deviation of the data points that are less than or equal to 0.5 but greater than or equal to -0.5 within my overall data.

推荐答案

使用这个:

=STDEV(IF((A1:A1000>=-0.5)*(A1:A1000<=0.5),A1:A1000))

根据版本不同，退出编辑模式时可能需要使用 Ctrl-Shift-Enter 来输入数组，而不是 Enter.

Depending on ones version this may need to be array entered with Ctrl-Shift-Enter when exiting edit mode instead of Enter.

同样是数组公式，A1:A1000 应该改为实际数据集.

Also being an array formula the A1:A1000 should be changed to the actual dataset.

或者如@JohnColeman 所示:

Or as @JohnColeman showed:

=STDEV(IF(ABS(A1:A1000) <= 0.5, A1:A1000))

甚至更短.它仍然与上述原始公式具有相同的需求.

is even shorter. It still has the same needs as the original formula stated above.

这篇关于在excel中使用标准偏差函数来满足特定条件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！