10

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.

Tasos
  • 7,325
  • 18
  • 83
  • 176
  • Have you ensured you've committed that as an array formula? – XOR LX Aug 11 '14 at 14:38
  • @XORLX You are right. I applied it as an array formula but the F4 cell is static. Do you know how I can do it dynamic for all the cells? – Tasos Aug 11 '14 at 16:06
  • 2
    You need to put the formula in a single cell first, then apply CTRL+SHIFT+ENTER....and then copy down – barry houdini Aug 11 '14 at 16:24
  • Great! That works. I, first, chose the whole area and applied the array formula there. Thank you – Tasos Aug 11 '14 at 16:26

1 Answers1

9

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

pnuts
  • 58,317
  • 11
  • 87
  • 139