CODEWITHSUNDEEP
excelexcel-formula
Daniel Scott
Daniel Scott

Reputation: 21

Using standard deviation function in excel for a specific criteria?

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.

Upvotes: 2

Views: 2274

Answers (3)

Scott Craner
Scott Craner

Reputation: 152505

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.

Upvotes: 2

AnAdverb
AnAdverb

Reputation: 33

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?

Upvotes: 0

Nate W
Nate W

Reputation: 320

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.

Upvotes: 0

Related Questions