Calculating Standard Deviation with If in Excel

excel excel-2013
Tasos picture Tasos · Aug 11, 2014 · Viewed 77.1k times · Source

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.

Answer

pnuts picture pnuts · Dec 17, 2014

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

Related questions

If two cells match, return value from third

Here's a simple explanation of what I'm having trouble with. Column A: List of 2300 order numbers Column B: Email Address associated with an order number Column C: List of 100 specific order numbers that I need the email address for So, …

Read More
Excel: Searching for multiple terms in a cell

I use this handy equation to search for a term inside of a cell in excel. =IF(ISNUMBER(SEARCH("*Gingrich*",C1)),"1","") This equation searches for the presence of Gingrich in C1, if it exists, it displays a 1. All I'd like …

Read More
Clear contents and formatting of an Excel cell with a single command

If you want to clear the contents of a cell or range in Microsoft Excel, you can use .ClearContents. If you also want to clear the formatting, you can use .ClearFormats. Sheets("Test").Range("A1:C3").ClearContents Sheets("Test").Range("…

Read More