How To Enable Gaming Mode on Android 12

Back in Februry, folks at XDA had spotted an unreleased Gaming Dashboard in the works for Android 12. But up until now, there were no clear...

Tuesday

Two Ways to Specify Blank Cells in Google Sheets Formulas

Posted by   on

You may already know how to specify blank cells in Google Sheets formulas.


But still, you are here because you may only know one way to do that, and it seems to make you feel the topic is quite interesting.


First of all, let me shed some light on the topic.


We may often want to specify blank cells in Google Sheets formulas mainly for two different purposes. Do you know what they are?



  1. To return an empty cell(s) as a formula output.

  2. As a condition in a formula to include or exclude blank rows.


Note:- Both may or may not come in a single formula.


When it comes to specifying blank or empty cells in Google Sheets, you can follow, mainly, two ways and let me call it;



  1. Blank character (double quotes) approach.

  2. Logical (IF test) approach.


I know most of you are well conversed with the blank character approach that by using double-quotes.


How many of you know it won’t work in all the cases, and we should follow the Logical (IF test) approach?


OK, we will discuss that at a later stage.


How to Specify Blank Cells in Google Sheets Formulas?


1. Blank Character (Double Quotes) Approach


It’s the usual approach and in which we can specify blank cells as follows.


Single:-


""

Multiple (Vertically):-


We can create an array of blank cells using the Curly Braces as below.


"";"";""

Multiple (Horizontally):-


Depending on your Locale settings, you can use either of the below formulas.


"","",""

If you are in EU countries, this may work.


=""\""\""

2. Logical (IF Test) Approach


Single:-


if(,,)

You May Like: How to Use IF Function in Google Sheets – Advanced Tips.


Multiple (Vertically):-


Here also we can create an array of blank cells using the Curly Braces.


if(,,);if(,,);if(,,)

Multiple (Horizontally):-


Here also, the Local settings may affect the output. Try either of the below formulas.


if(,,),if(,,),if(,,)

If you are in EU countries, this may work.


=if(,,)\if(,,)\if(,,)

Formula Examples


At the beginning of this tutorial, I have mentioned the two purposes of specifying blank cells in Google Sheets formulas.


Here are examples of both of them using the above two approaches.


Return an Empty Cell as a Formula Output (Purpose 1)


Sample Data:-


The value in cell B2=”Name” and C2=”Stuart.”


The following formula in cell E2 tests whether the text string in cell B2 is “Name.”


=if(B2="Name",C2,"")

If it evaluates to TRUE, it returns the name (text string) in cell C2.


If it evaluates to FALSE, it returns blank.


Above, I have followed the Blank Character (Double Quotes) Approach to return a blank cell.


In cell E3, I have the following formula, which is similar to the E2 one. The only difference is, here I have followed the Logical (IF Test) Approach to return a blank cell.


=if(B2="Name",C2,if(,,))

Specifying Blank Cells in Google Sheets Formulas - Example

Specifying Blank Cell as a Condition in a Formula (Purpose 2)


Sample Data:-


Cell Range B2:B10 contains a few text strings and empty cells.


I want to extract only the text strings. With the help of the FILTER function, we can do that.


Here is how to specify blank cells as a condition in a Filter formula in Google Sheets.


Formula Using Blank Character (Double Quotes) Approach:-


=filter(B2:B10,B2:B10<>"")

Formula Using Logical (IF Test) Approach:-


=filter(B2:B10,B2:B10<>if(,,))

The above applies to almost all the functions, except Database functions.


In Database Formulas, it seems only the Logical (IF Test) Approach works. You can find an example below.


Specifying Blank Criteria Cells in Database Formulas in Google Sheets


When it comes to getting expanding array results in row or column-wise, Database functions such as DMIN, DMAX, DSUM, DCOUNT are my choice.


While coding such formulas, we may require to specify empty criteria.


Here I am not going to detail how to use Database functions to return expanding results.


Because that you can find under the tag row-wise array.


Here I will show you how to specify blank/empty criteria in Database functions.


For the example purpose, I am taking the function DSUM.


=dsum(B2:C9,2,E2:E3)

It returns the total ‘Quantity’ based on the condition in E2:E3.


How to Specify Empty Cells in Criteria in Database Functions

Here to avoid using criteria/conditions, you may remove the values in E2:E3.


But I don’t want to refer to cell E2:E3.


Then how to specify two blank cells within the DSUM function in Google Sheets?


=dsum(B2:C9,2,"";"")

Nope! It won’t work.


But the following formula will!


=dsum(B2:C9,2,if(,,);if(,,))

In short, to specify blank/empty cells within the Database functions, follow the Logical (IF Test) Approach, not the Blank Character (Double Quotes) Approach.


As far as I know, in other category functions, both approaches will equally work well.


That’s all. Thanks for the stay. Enjoy!

No comments:
Write Comments

Hello Friends, welcome to autobloginc.blogspot.com we Hope You'll like it - COntact US
!!THANK YOU FOR YOUR SUPPORT!!