Google-sheets – Google Sheets Blank Cells with a function

google sheets

How do I change a simple subtraction function =B3-A3 to return a blank cell if either of the 2 cells is blank?

Best Answer

Short answer

=IF(OR(ISBLANK(B3),ISBLANK(A3)),"A3 or B3 are blank",B3-A3)

Explanation

Use ISBLANK to test if cell is blank
Use OR to check if any of its arguments is true
Use IF to return something if the condition returns true and other thing if the condition returns false

Related Solutions

Google-sheets – Return a blank when cell reference is blank

Google Sheets as well as other spreadsheet application handles dates as serialized number. The large number that you refer that is displayed when a cell on AL column is blank is very likely that it is the number that represents the date on the corresponding cell on column G.

Regarding returning a blank, try something like this:

=ArrayFormula(IF(ISBLANK(A:A),,"Non blank"))

Please note that the second argument of the IF() function is left empty (,,): IFS(), IF() and other similar functions return blank when the corresponding argument is empty.

Google Sheets – How to Replace Blank Cells with Zeros

I assume you only want to do this in a certain part of the spreadsheet, and that it's a one-off problem which you need to fix, not something you need to repeat.

For this, the simplest approach is to find another area the same shape and size (perhaps in another worksheet), and not currently used.

In each cell in that area, set the forumula to something like this (depending on the cells you use):

=if(isblank(A3),"0",A3)

The select the data from the 2nd area, copy it, and paste it back to the original using Edit > Paste Special > Paste values only

You may need to re-apply some of the formatting (eg date formats), but the values will be there.

fyi, I have put a worked example of this approach here: https://docs.google.com/spreadsheets/d/1T6owfGtpKt3NLl5hq1AZpeWA1ILWUwBQ44bdlAmekF0/edit?usp=sharing