Menu
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.
950
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
133
answered Sep 24 '22 12:09
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
