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))
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?
Edit: 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.
Using the numbers listed in column A, the formula will look like this when applied: =STDEV. S(A2:A10). In return, Excel will provide the standard deviation of the applied data, as well as the average.
The STDEV. S function is used when calculating the standard deviation for an entire population. If we are calculating the standard deviation of an entire population, we need to use the STDEV. P function.
IF is one of the Logical functions in Microsoft Excel, and there are 3 parts (arguments) to the IF function syntax: logical_test: TEST something, such as the value in a cell. value_if_true: Specify what should happen if the test result is TRUE. value_if_false: Specify what should happen if the test result is FALSE.
For the sake of an answer (courtesy @barry houdini):
You need to put the formula in a single cell first, then apply CTRL+SHIFT+ENTER....and then copy down
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With