ExcelInsider Team
Standard deviation is the variation or dispersion in a set of observations. Sometimes, you may want to calculate the standard deviation for a subset of your data based on a specific condition. You can do this easily by combining Excel functions.
➤ Standard deviation is the variation or dispersion in a set of observations.
➤ To calculate the standard deviation for a subset of your data based on a specific condition combine various Excel functions.
➤ Single Criteria:
-
=STDEV.S(IF(cell_range="value",cell_range))=STDEV.S(FILTER(return_array,cell_range="value",[if_empty]))=DSTDEV(database,field,criteria)
➤ Multiple Criteria:
-
=STDEV.S(IF((cell_range="value1")*(cell_range="value2"),cell_range))=STDEV.S(FILTER(array,(cell_range="value1")*(cell_range="value2"),[if_empty]))
In this article, we’ll learn to calculate the standard deviation with if condition for single and multiple criteria using the STDEV.S, DSTDEV, IF, and FILTER functions.
Standard Deviation with IF Condition (Single Criteria)
In this dataset, we have the sales data for three products (Cell phone, PC, and Printer). Let’s calculate the standard deviation of the sales for the three products.
STDEV.S and IF Functions:
➤ Select the output cell (E3) and enter the formula. For Excel 2019 or earlier versions, press the Ctrl+Shift+Enter keys. On Excel 365 or later versions just press Enter . The standard deviation for PC sales is 457.17.
=STDEV.S(IF(A2:A16="PC",B2:B16))
=STDEV(IF(A2:A16="PC",B2:B16)). If your data represents the entire population use the STDEV.P or STDEVP function.
STDEV.S and FILTER Functions:
➤ Combine the STDEV.S and FILTER functions to calculate the conditional standard deviation for Printer sales (62.92).
=STDEV.S(FILTER(B2:B16,A2:A16="Printer","N/A"))
DSTDEV Function:
➤ Use the database DSTDEV function to calculate the standard deviation for cell phone sales (341.69).
=DSTDEV(A1:B16,B1,D6:D7)
Standard Deviation with IF Condition (Multiple Criteria)
Let’s calculate the standard deviation of the sales based on two criteria: product and region.
➤ Enter the formula in cell (F3) and press Ctrl+Shift+Enter . The standard deviation of cell phone sales in the North is 390.51.
=STDEV.S(IF((A2:A16="Cell phone")*(B2:B16="North"),C2:C16))
➤ Using the STDEV.S and FILTER functions, the standard deviation for PC sales in the West is 621.15.
=STDEV.S(FILTER(C2:C16,(A2:A16="PC")*(B2:B16="West"),"N/A"))
FAQ
Can you do a standard deviation if in Excel?
Yes, you can combine the STDEV.S and IF functions.
Single Criteria: =STDEV.S(IF(cell_range="value",cell_range))
Multiple Criteria: =STDEV.S(IF((cell_range="value1")*(cell_range="value2"),cell_range))
Can STDEV be higher than mean?
Yes, the standard deviation (STDEV) can be higher than the mean value of a dataset. This indicates the values in the dataset are widely spread out around the mean.
Should I use the STDEV.S or STDEV.P function in Excel?
Use the STDEV.P or STDEVP if your dataset represents the entire population. If your data is a sample of the population then use the STDEV.S or STDEV function.
What is STDEVA in Excel?
The STDEVA function calculates the standard deviation of a sample. This function converts logical value TRUE as 1 and FALSE as 0. You should convert any text data to a suitable numeric scale.
How to calculate standard error in Excel?
The standard error (SE) provides an estimate of the variability or precision of a sample statistic relative to the population statistic.
SE: =STDEV.S / SQRT(sample size)
Wrapping Up
In this tutorial, we’ve shown several examples of how to calculate the standard deviation with an if condition in Excel. We have used the STDEV.S, DSTDEV, IF, and FILTER functions. Feel free to download the practice file and let us know preferred method.
