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.

**Table of Contents**hide

## 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.