The divisor in the standard deviation formula is different depending on whether you want the standard deviation for a data set that represents the entire population (divide by the number of data elements minus one), or if your data set is a sample of the population, and you want to calculate the standard deviation to generalize your results to the entire population (divide by the number of data elements). As a result, Excel provides the following four functions to calculate standard deviation based on whether you want to calculate the statistic for a population or a sample, and how you want to treat text and logical values in your data set:
- STDEV.P - Calculates the standard deviation for a population and ignores logical and text values.
- STDEV.S - Calculates the standard deviation for a sample and ignores logical and text values.
- STDEVPA - Calculates the standard deviation for a population and converts "False" and logical False to zero and "True" and logical True to 1.
- STDEVA - Calculates the standard deviation for a sample and converts "False" and logical False to zero and "True" and logical True to 1.
Video of the Day
Launch Excel and either open a workbook that has the data for which you want to calculate statistics or type the data into the blank workbook. For an example, consider a workbook with test scores for a population of 10 students that does not include any text or logical data. The workbook also contains an area in columns D and E to display the calculated statistics.
Click in the cell that will display the mean value for the population and then choose the function selector, fx.
Change the category selection box to Statistical. Scroll through the list of functions, choose AVERAGE and then select OK.
Click the cell selector button to choose the cells that contain your data.
Use your mouse to highlight the range of data cells or type the range into the Function Arguments box, for example, B2:B11. Choose OK to place the calculation in your workbook.
Calculate Standard Deviation
Click the cell that will display the standard deviation and click the function selector, fx.
Choose the STDEV.P formula to calculate the standard deviation for the entire population and to ignore text and logical values. The category selection box defaults to Statistical based on your previous selection. Choose OK to continue.
Click the cell selector button to select the range of values.
Select the range of data cells or type the range in the Function Arguments box and choose OK.
Right-click in the cell that displays the standard deviation and choose Format Cells... from the menu.
Select the number of decimal places you want to display and choose a format for negative numbers.
Review the results to ensure they look reasonable given the data set.