SA W/ Excel For Dummies (Notes) : The missing functions STDEVIF and STDEVIFS

Here's a rule of thumb:  Whenever you present a mean, provide a standard deviation.  Use AVERAGE and STDEV.S or STDEV.P in tandem.

Remember that Excel offers two functions, AVERAGEIF and AVERAGEIFS for calculating means conditionally.  Two additional functions would have been helpful STDEVIF and STDEVIFS for calculating standard deviations conditionally.

Excel however doesn't provide these functions.  Shown below are a couple workarounds that enable you to calculate standard deviations conditionally.  The workarounds filter out data that meet a set of conditions, and then calculate the standard deviation of the filtered data.  The data below is from a fictional psychology experiment:

Cell F19 presents the standard devation (STDEV.S) for the trials that displayed a circle and is equivalent to: =STDEVIF (Shape, "Circle", RT_msec if this function existed.

Cell H 19 (not pictured but 66.44) shows the standard deviation for a green square and is equivalent to:
=STDEVIFS (RT_msec, Color "Green", Shape, "Square" if that function existed.

So how to filter the data:

First a little logic

In order to proceed you have to know about two of Excel's logic functions: IF and AND.  You access them by clicking Formulas | Logical Functions

IF takes three arguments:

A logical condition to be satisfied
The action to take if the logical condition is satisfied (that is, if the value of the logical condition is TRUE)
An optional argument that specifies the action to take if the logical condition is not satisfied (that is, if the value of the logical condition is FALSE)

AND can take up to 255 arguments.  AND checks to see if all of its arguments meet each specified condition - that is, each condition is TRUE.  If they all do, AND returns the value TRIE.  If not, AND returns the value FALSE.

In this example, I use IF to set the value of a cell in Column F to the corresponding value in Column D if the value in the corresponding cell in Column C is a circle.  The formula is F2 is = IF (C2="Circle, D2, " ").  If this were a phrase it would be, "If the value in C2 is "Circle", then set the value of this cell to the value in D2.  If not, leave the cell blank.  Autofilling the next 15 cells of Column C yields the filtered data pictured above.

I use And along with IF for the cells in column H.  Each one holds the value from the corresponding cell in Column D if two conditions are true:

The value in the corresponding cell in Column B is "Green"
The value in the corresponding cell in Column C is "Square"

The formula for column H is = IF(AND(B2="Green", C2="Square"),D2," ")