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

formulasgoogle sheets

I am using the following formula in a Google Sheets to calculate the number of days between two dates (dates are in columns G and AL):

=ARRAYFORMULA(if(row(A:A)=1, "Readmission Days", (G:G)-(AL:AL)))

It has to be an array because I am working with a Google Forms and entries are automatically populated into new rows.

The problem is that some of the cells in col AL are blank, so the formula is reading those dates as something else and returning a very large number. Also, when both cells are blank, the formula returns a 0, messing up my data.

How can I include something to ignore blank cells or just return a blank when one or both cell references is blank?

Best Answer

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.

Short answer

Use ARRAY_CONSTRAIN(array,row_limit,column_limit)

Explanation

ARRAY_CONSTRAIN function could return the number of rows and columns that you want, just set the row_limit and column_limit parameters accordingly. To automatically set this parameters you could use a function like COUNT or COUNTA.

To calculate the number of rows/columns considering the data range by using built-in functions,

for rows use a formula like the following

=MAX(IF(LEN(A2:A)>0,ROW(A2:A)-1,0))

for columns use a formula like the following

=MAX(IF(LEN(1:1)>0,COLUMN(1:1)-1,0))

The sample formula in the question will look like the following:

=ARRAYFORMULA(
   ARRAY_CONSTRAIN(
     MMULT(N(ROW(A2:A)>=TRANSPOSE(ROW(A2:A))),SIGN(ROW(A2:A))),
     MAX(IF(LEN(A2:A)>0,ROW(A2:A)-1,0)),
     MAX(IF(LEN(1:1)>0,COLUMN(1:1)-1,0))
   )
)

Best Answer

Related Solutions

Google-sheets – How to check if a cell is blank for Conditional formatting

Google-sheets – How to block automatic row and column insertion in Google Sheets

Short answer

Explanation

Related Topic