This article will guide you in cases where you may need to use google sheets average if the cell is not blank. Google Sheets includes functions to compute the average of a number in a range of cells and the average of cells based on particular parameters. Functions like **AVERAGE** and **AVERAGEIF** are two examples. However, there are occasions when the data in a cell is Blank, Text, or contains 0 (Zero), which may affect the results. This article will therefore assist you in efficiently handling them.

## A Sample of Practice Spreadsheet

You can copy the spreadsheet that we’ve used to prepare this article.

## 5 Methods to Average If Cell Is Not Blank in Google Sheets

The **AVERAGE** function is one of the core functions available in Google Sheets. There are various uses for it, depending on the circumstance and requirements. Below, we’ll show possible scenarios.

### 1. Using the AVERAGE Function

Assume you have all of the results from a student who participated in two quizzes. You now wish to determine his overall score across the two tests. However, In **Quiz 1**, there is an empty cell in **C7**. We will apply the following steps.

**Steps: **

- Select cell
**C11**. - We will use the following
**formula:**

`=AVERAGE(C5:C9)`

- Press
**ENTER**to see the result.

The **AVERAGE** function is going to ignore the blank cell and return a value of **12** by summing all the numbers and dividing them by 4.

In the second **quiz**, he was **absent** from the **science** quiz. We are going to use the same **AVERAGE** function.

**Steps:**

- Go to cell
**D11**. - We will be using the following
**formula:**

`=AVERAGE(D5:D9)`

- Again, press
**ENTER**to get the result.

Again, in this case, the function will not take into account the text in cell **D6** and will instead return a value of **15**, which is the average of the four subjects.

**NOTE:**

**AVERAGE**function automatically ignores any cells that are blank or that contain text.

### 2. Applying AVERAGEA Function Taking Text Value as Zero

In the previous example, we can mark **Absent** as zero and find his overall score just by using the **AVERAGEA** function.

**Steps:**

- Select cell
**D11**. - Write down the following
**formula:**

`=AVERAGEA(D5:D9)`

- Press
**ENTER**to view to result.

We can observe that his **average** fell from **15** to **12** when compared to the prior result. Therefore, while determining the average of any set of data, we can treat any text as zero using the **AVERAGEA** formula.

### 3. Utilizing AVERAGEIF Function with Criteria

The best formula to utilize in Google Sheets if a condition or criterion needs to be satisfied to calculate an average is **AVERAGEIF**. For a better understanding of the formula, we will provide two scenarios with examples.

#### 3.1 AVERAGEIF for Not Blank Criteria

We are going with the same dataset as before, however, there will be a slight change. Let’s say that due to some reason, the **history** quiz was canceled and it wouldn’t be counted. Therefore, now we are going to average the subjects which are not blank in the subjects column.

**Steps:**

- Go to cell
**D11**. - Use the following
**formula:**

`=AVERAGEIF(B5:B9,"<>",C5:C9)`

- Press
**ENTER**.

As you can see, the function disregarded the number **20** in cell **C7** and returned a value of **12**, which is the average of the four subjects.

#### 3.2 Ignoring Zero (0) While Averaging

Using the same database and formula we can also exclude the value **0** (zero). In **Quiz 2**, the student scored a **zero**. If we look at the previous examples, it will be in the **History** exam, which was canceled later. Therefore now we are going to count the average excluding the **0** using the following process.

**Steps:**

- Click cell
**D11**. - Use the following
**formula:**

`=AVERAGEIF(D5:D9,"<>0")`

- Finally, press
**ENTER.**

We expect the** Quiz 2** result to have an arithmetic mean of 12.8. Since the **0** in cell **D7** has been disregarded, we are now working with the other four subjects. The total mark, 64, was then divided by 4 using the **AVERAGEIF** method.

**Limitation:**

**AVERAGEIF**function does not count text value and automatically ignores it.

### 4. Using AVERAGEA and IF Functions

We are going to use a similar example again but with a slight difference in it. Assume that the student was **absent** in two quizzes, **Science** in the first and **Foreign Language** in the second. We are now going to calculate the average of the five subjects, nevertheless, the cell contains a blank cell or text. It will count the value as zero.

**Steps:**

- Select cell
**C11**. - We will be using the following formula in google sheets.

`=ARRAYFORMULA(AVERAGEA(IF(B5:B9<>"",C5:C9)))`

- After closing the bracket, press
**ENTER**.

The **ARRAYFORMULA** is used to verify whether the name of each subject is not blank before only using the marks from subjects whose names are not blank in the **AVERAGEA** Function. The subject name is blank, hence the value **20** is ignored. The **AVERAGEA** Function treats the text “**Absent**” as 0, resulting in a calculation of an average of **7.2**.

We’re going to apply the following formula on **Quiz 2**.

**Steps:**

- Go to cell
**C11**. - Use the following
**formula:**

`=ARRAYFORMULA(AVERAGEA(IF(B5:B9<>"",D5:D9)))`

- Press
**ENTER**to see the result.

Same as before, this time there is a **0** in the cell **D7**, which is also counted as zero in the formula. Thus delivering an average of **9.2**.

**NOTE:**Use this formula if you want to count all the blank cells and text as Zero to get an average from any data.

### 5. Applying IF, COUNT, and AVERAGE Formula

Only if any cell in the range is blank, can you use the following formula with a combination of **IF**, **COUNT,** and **AVERAGE** functions to determine the average value of a range in Google Sheets. Three choices are available, depending on the criterion. Such as:

#### 5.1 Column Contains a Blank Cell

Assume for this example that the student did not participate in one subject during the **first quiz** and did not participate at all during the **second quiz**. You must, however, obtain the overall average of the student.

**Steps:**

- Select cell
**C11**. - This time, we’ll calculate the average using the formula below.

`=IF(COUNT(C5:C9),AVERAGE(C5:C9),0)`

- To get your result, press
**ENTER**.

**Formula Explanation:**

**COUNT(C5:C9)**is theof the**logical_expression****IF**function.- If the value is TRUE or returns any numerical value, it will activate
**AVERAGE(C5:C9)**. - If it does not return any value or FALSE, it will show
**“0”**.

We can see that in **Quiz 1** Column, The formula gave us the average of the non-missing data which is **12**. Despite the missing data, the formula simply counts the data-filled cells.

#### 5.2 Return Zero (0) for Missing Values

Same example and formula as before but we will calculate an average for the **Quiz 2** column.

**Steps:**

- Click cell
**C11**. **Formula:**

`=IF(COUNT(D5:D9),AVERAGE(D5:D9),0)`

- Press
**ENTER**to finalize.

**Formula Explanation:**

**COUNT(C5:C9)**will act as the logic of the**IF**function.**AVERAGE(C5:C9)**will activate If the value is TRUE or returns any numerical value.**“****0****“**, If it does not return any value or FALSE.

For all the missing values, however, it returned zero in the case of **Quiz 2**.

#### 5.3 Return Blank for Missing Values

If you don’t want to have any numerical value for any blank cells, You can use the following formula to get a blank result.

**Steps:**

- Select cell
**D11**. **Formula:**

`=IF(COUNT(D5:D9),AVERAGE(D5:D9),"")`

- Press
**ENTER**to view to result.

**Formula Explanation:**

- The
**IF**function will use**COUNT(C5:C9)**as its logic. - If the value is TRUE,
**AVERAGE(C5:C9)**will be activated. **“”**represents a**blank cell.**In case of the formula not returning any value or FALSE, it will return the blank cell.

In the image above, it is clear that the formula returned a **blank value** as the average of all the blank cells in **Quiz 2**.

## Final Words

Depending on your needs, you can employ any strategy you choose. In our daily lives, the average mathematical formula is crucial for determining something’s core value. When using Google Sheets, the dataset can be blank, text, or zero, among other things. Therefore, if you don’t know how to use it, finding an average if not blank, can be a hassle.

Visit our website, **OfficeWheel**, for more information.