Is There a Standard Deviation IF Function in Excel?

While Excel 2013 doesn't have a dedicated standard deviation IF function, you can make standard deviation a condition of an IF function by using a method called “nesting,” where one function is placed inside of another. Excel enables both a conditional IF as well as a calculation of standard deviation using the STDEVA or STDEVPA functions, which calculate based on sample and entire populations, respectively.

Produce a Result Based on Deviation

Nest a standard deviation within an IF statement by placing the standard deviation first. Doing so creates an IF condition based on the results of the standard deviation. The following formula calculates the standard deviation of a range, then returns the words “High variance” or “Low variance” based on the results:

\=IF(STDEVPA(A1:A35)>10, “High variance”, “Low variance”)

The first field is the IF test that checks to see if the standard deviation is higher than 10, the second comma-delimited field is the value returned when the deviation is higher than 10, and the third comma-delimited field is the value shown when the standard deviation is not higher than 10.

Calculate Deviation Based on Condition

You can also use IF to only calculate deviation when a certain condition is met. For example:

\=IF(COUNTA(A1:A100)<30, STDEVA(A1:A100), STDEVPA(A1:A100)

This calculates a standard deviation of a population sample, also called STDEVA, when the total number of values is less than 30. When the number of samples is greater than 30, it calculates the standard deviation of a whole population. The COUNTA function counts the number of cells that are not empty.