I tried :
我试过了 :
=STDEV(IF((COUNTIFS(AM$9:AM$89,">"&AP10,AM$9:AM$89,"<="&AP11)),AN9:AN89))
It gave me the STDEV of whole second column.
它给了我整个第二列的STDEV。
The main objective is I want to make histogram with their standard deviation as an error bar.
主要目标是我想将直方图的标准偏差作为误差条。
2 个解决方案
#1
0
You can solve this in 2 steps. First, you will need an Array Formula, which works similar to what you are attempting with your countifs function. In short, it takes a whole range of values and uses then to run the formula multiple times, spitting out an array of answers. To get your array of numbers larger than AP10, do this:
您可以通过两个步骤解决此问题。首先,您需要一个数组公式,其工作方式与您尝试使用countifs函数类似。简而言之,它需要一系列的值,然后使用多次运行公式,吐出一系列答案。要使您的数字数组大于AP10,请执行以下操作:
=IF(AM3:AM89>AP10,AM3:AM89,"")
To confirm this formula, press CTRL + SHIFT + ENTER instead of just ENTER. This will give you a range of values from AM3:AM89, with a blank replacing any value not bigger than AP10. These blank values will be ignored by the STDEV formula. So just wrap the above array formula in STDEV as in the below, and you're done:
要确认此公式,请按CTRL + SHIFT + ENTER而不是ENTER。这将为您提供AM3:AM89的一系列值,空白替换任何不大于AP10的值。 STDEV公式将忽略这些空白值。所以只需将上面的数组公式包装在STDEV中,如下所示,您就完成了:
=STDEV(IF(AM3:AM89>AP10,AM3:AM89,""))
Remember - confirm with CTRL + SHIFT + ENTER instead of just ENTER. For background on array formulas, see here: http://spreadsheets.about.com/od/excelfunctions/ss/2011-03-23-excel-2010-multi-cell-array-formula-sbs-tutorial.htm
记住 - 用CTRL + SHIFT + ENTER确认,而不是只用ENTER确认。有关数组公式的背景信息,请参见此处:http://spreadsheets.about.com/od/excelfunctions/ss/2011-03-23-excel-2010-multi-cell-array-formula-sbs-tutorial.htm
#2
0
The standard set-up would be the array formula**:
标准设置将是数组公式**:
=STDEV(IF(AM$9:AM$89>AP10,IF(AM$9:AM$89<=AP11,AN9:AN89)))
although it's interesting to note that you can also use a different array formula** by "inverting" your COUNTIFS construction:
虽然有趣的是要注意你也可以通过“反转”你的COUNTIFS结构使用不同的数组公式**:
=STDEV(IF(COUNTIFS(AP10,"<"&AM$9:AM$89,AP11,">="&AM$9:AM$89),AN9:AN89))
which may well be more efficient.
这可能更有效率。
Regards
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
**数组公式的输入方式与“标准”公式的输入方式不同。您只需按住CTRL和SHIFT键,然后按ENTER键,而不是只按ENTER键。如果你已经正确地完成了它,你会注意到Excel在公式周围放置了大括号{}(尽管不要尝试自己手动插入这些)。
#1
0
You can solve this in 2 steps. First, you will need an Array Formula, which works similar to what you are attempting with your countifs function. In short, it takes a whole range of values and uses then to run the formula multiple times, spitting out an array of answers. To get your array of numbers larger than AP10, do this:
您可以通过两个步骤解决此问题。首先,您需要一个数组公式,其工作方式与您尝试使用countifs函数类似。简而言之,它需要一系列的值,然后使用多次运行公式,吐出一系列答案。要使您的数字数组大于AP10,请执行以下操作:
=IF(AM3:AM89>AP10,AM3:AM89,"")
To confirm this formula, press CTRL + SHIFT + ENTER instead of just ENTER. This will give you a range of values from AM3:AM89, with a blank replacing any value not bigger than AP10. These blank values will be ignored by the STDEV formula. So just wrap the above array formula in STDEV as in the below, and you're done:
要确认此公式,请按CTRL + SHIFT + ENTER而不是ENTER。这将为您提供AM3:AM89的一系列值,空白替换任何不大于AP10的值。 STDEV公式将忽略这些空白值。所以只需将上面的数组公式包装在STDEV中,如下所示,您就完成了:
=STDEV(IF(AM3:AM89>AP10,AM3:AM89,""))
Remember - confirm with CTRL + SHIFT + ENTER instead of just ENTER. For background on array formulas, see here: http://spreadsheets.about.com/od/excelfunctions/ss/2011-03-23-excel-2010-multi-cell-array-formula-sbs-tutorial.htm
记住 - 用CTRL + SHIFT + ENTER确认,而不是只用ENTER确认。有关数组公式的背景信息,请参见此处:http://spreadsheets.about.com/od/excelfunctions/ss/2011-03-23-excel-2010-multi-cell-array-formula-sbs-tutorial.htm
#2
0
The standard set-up would be the array formula**:
标准设置将是数组公式**:
=STDEV(IF(AM$9:AM$89>AP10,IF(AM$9:AM$89<=AP11,AN9:AN89)))
although it's interesting to note that you can also use a different array formula** by "inverting" your COUNTIFS construction:
虽然有趣的是要注意你也可以通过“反转”你的COUNTIFS结构使用不同的数组公式**:
=STDEV(IF(COUNTIFS(AP10,"<"&AM$9:AM$89,AP11,">="&AM$9:AM$89),AN9:AN89))
which may well be more efficient.
这可能更有效率。
Regards
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
**数组公式的输入方式与“标准”公式的输入方式不同。您只需按住CTRL和SHIFT键,然后按ENTER键,而不是只按ENTER键。如果你已经正确地完成了它,你会注意到Excel在公式周围放置了大括号{}(尽管不要尝试自己手动插入这些)。