在Excel中使用If计算标准偏差 [英] Calculating Standard Deviation with If in Excel

问题描述

我有一个包含两列的数据集.一个标签和一个号码.使用sumif和countif，我设法计算出每个标签在组中的平均值.现在，我正在尝试对标准偏差进行相同操作，但是我有一个问题:

I have a dataset with two columns. One label and one number. Using sumif and countif, I managed to calculate the average of each label in groups. Now, I am trying to do the same with Standard Deviation but I have a problem:

=STDEV.P(IF($A$2:$A$625129=F4,$B$2:$B$625129))

这是我使用的公式，其中A列是带有标签的列，B带有数字，而F4是标签组之一.结果为0.我对每个标签组都执行相同操作，所有标签组均为0.您知道公式中有什么问题吗?

This is the formula I use, where A column is the column with the labels, B with the numbers and F4 is one of the label groups. The result is 0. I do the same for every label group and all of them are 0. Any idea what is wrong in the formula?

在评论之后，我尝试将公式作为数组之一应用，并且几乎可以正常工作.现在，唯一的问题是单元格F4是静态的，而我希望它对于数组公式中的每个单元格都是动态的(F5，F6，F7等).

After the comment, I tried to apply the formula as an array one and it almost worked. Now, the only problem is that the cell F4 is static whereas I want it to be dynamic (F5, F6, F7 etc) for every cell in array formula.

推荐答案

为了回答这个问题(由@barry houdini提供):

For the sake of an answer (courtesy @barry houdini):

您需要先将公式放在单个单元格中，然后应用CTRL + SHIFT + ENTER....然后向下复制

You need to put the formula in a single cell first, then apply CTRL+SHIFT+ENTER....and then copy down

这篇关于在Excel中使用If计算标准偏差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！