# How to Ignore 0 for Standard Deviation Formula in Google Sheets

There are several functions like STDEV, STDEVA, STDEVP, etc. in Google Sheets that can measure the standard deviation for a range. These functions take a numerical data range as input and return the standard deviation of that data range. Sometimes, the data range we provide may contain 0 values. There can be several reasons behind a 0 value, but often we enter a 0 value for inappropriate cases. Therefore, including the cell in a data range may cause the mentioned functions to return a standard deviation that doesn’t express the actual spread of given data. Hence, the question arrives: “Can we use the Standard Deviation formula ignoring 0 values?”. The answer is a resounding Yes! And in this article, I’ll demonstrate 3 simple ways to ignore 0 values while using a Standard Deviation formula (the STDEV formula) in Google Sheets.

## A Sample of Practice Spreadsheet

You can copy our practice spreadsheets by clicking on the following link. The spreadsheet contains an overview of the datasheet and an outline of the described ways to use the Standard Deviation formula ignoring 0 in Google Sheets.

## What Is Standard Deviation?

The standard deviation is a parameter in statistics to measure the amount of variation or dispersion in a given dataset. A lower standard deviation value indicates that the values of the dataset are closer to the mean value, while a higher standard deviation indicates that the values are in a wider span in that data range.

## 3 Simple Ways to Ignore 0 for Standard Deviation Formula in Google Sheets

First, let’s get familiar with our dataset. The dataset contains a list of hundreds scored by several cricket players from various countries. The STDEV function is used for calculating the standard deviation of the given data in Cell D15. As you can see one of the players hasn’t scored any hundreds. Therefore, the standard deviation is quite high here. But since that player is not a proper batsman, including him in this range doesn’t provide an accurate insight into this data. Therefore, we’ll ignore the cells with a 0 value and calculate the standard deviation for the remaining cells. Keep reading to learn how. ### 1. Combining STDEV and FILTER Functions

The FILTER function can be combined with the STDEV function to ignore cells with 0 values and return the standard deviation of the remaining cells.

Steps:

• First, select Cell D16.
• Afterward, type in the following formula-
`=STDEV(FILTER(D5:D13,D5:D13<>0))`
• Finally, press Enter key to get the required result. Formula Breakdown

• FILTER(D5:D13,D5:D13<>0)

First, the FILTER function returns the filtered version of the source data range D5:D13 by ignoring cells with 0 values.

• STDEV(FILTER(D5:D13,D5:D13<>0))

And then the STDEV function returns the standard deviation of the filtered range.

### 2. Uniting STDEV, ARRAYFORMULA, and IF Functions

Another way to ignore 0 values while using the standard deviation formula STDEV is to combine it with the ARRAYFORMULA and IF functions.

Steps:

• First, select Cell D16.
• After that, type in the following formula-
`=ARRAYFORMULA(STDEV(IF(D5:D13<>0,D5:D13)))`
• Finally, press Enter key to get the required standard deviation. Formula Breakdown

• IF(D5:D13<>0,D5:D13)

First, the IF function returns any cell within the range D5:D13 if it is not equal to zero.

• STDEV(IF(D5:D13<>0,D5:D13))

After that, the STDEV function calculates the standard deviation of the cells returned by the IF function.

• ARRAYFORMULA(STDEV(IF(D5:D13<>0,D5:D13)))

Here, the ARRAYFORMULA function helps the non-array function IF to Deal with an array.

### 3. Formatting 0 Values as Plain Text

Perhaps, this is the simplest way to ignore 0 values while using the STDEV function. Since the STDEV function simply ignores text values, we can set plain text format for the cells that contain 0 values. However, for large datasets, many of the cells can contain 0 values. Therefore, manually changing the data format isn’t very efficient.

Steps:

• First, select all the cells with 0 values and set the data format as “Plain Text” from the menu options. • Now, select Cell D15 and type in the following formula-
`=STDEV(D5:D13)`
• Finally, press Enter key to get the required result. ## Things to Be Considered

• The IF function is a non-array function. Therefore, if want to enter an array as an argument for the IF function, unite the ARRAYFORMULA function with it.
•  The STDEV function simply ignores text values.

## Conclusion

This concludes our article to learn how to ignore 0 values while using a Standard Deviation formula in Google Sheets. I hope the demonstrated ways were sufficient for your requirements. Feel free to leave your thoughts on the article in the comment box. Visit our website OfficeWheel.com for more helpful articles. #### Seemanto Saha

Hello, I am Seemanto Saha, and working as a Technical Writer and Content Creator at OfficeWheel.com. I completed my B.Sc. in Industrial and Production Engineering from Bangladesh University of Engineering and Technology. Being an enthusiast for solving analytical problems, I aim to leverage my research and analytical skills to create better content for everyone.

We will be happy to hear your thoughts 