Ishrak Khan
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.
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.
Read More: How to Average in Google Sheets If Value Is Not 0 (2 Simple Ways)
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.
Read More: How to Use AVERAGEIF Function in Google Sheets (6 Examples)
4. Using AVERAGE 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.
Read More: How to Find Average in Google Sheets (8 Easy Ways)
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 the logical_expression of the 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.
