I am currently working on a project and was wondering if there is a way to calculate the standard deviation of values in a specified range in a data set. For example, I would like to calculate the standard deviation of the data points that are less than or equal to 0.5 but greater than or equal to -0.5 within my overall data.
3 Answers
Use this:
=STDEV(IF((A1:A1000>=-0.5)*(A1:A1000<=0.5),A1:A1000))
Depending on ones version this may need to be array entered with Ctrl-Shift-Enter when exiting edit mode instead of Enter.
Also being an array formula the A1:A1000 should be changed to the actual dataset.
Or as @JohnColeman showed:
=STDEV(IF(ABS(A1:A1000) <= 0.5, A1:A1000))
is even shorter. It still has the same needs as the original formula stated above.
A possible solution would be to create another column next to your column of data, and create a formula in that column to only populate the number into the cell value if the cell meets your filtering criteria. For example, something like this, assuming your data is in column A:
=IF(OR(A1>0.5,A1<-0.5),A1,"")
Then take the standard deviation of column B.
Should be: IF(OR(A1>0.5,A1<-0.5),"",A1)
But otherwise, yes, Nate's answer is the quickest easiest way for getting the answer.
Is this a one-time thing, or do you need to constantly display the standard deviation of that subset on a table that will be updated?
Related