Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

I am using the following formula in a Google spreadsheet to calculate the number of days between two dates (dates are in columns G and AL). 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?

Here is my formula:

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

PS: The formula has to be an array because I am working with a Google form and entries are automatically populated into new rows.

share|improve this question

3 Answers 3

This formula will work to keep blank cells from returning "0" to another cell.

=IF( AND( ISBLANK(A1)=true , isblank(B1)=true ) , "dontsayzero") 

Then, you can just remove the dontsayzero after you've tested it, but LEAVE THE QUOTES! Also, you can make things more sophisticated adding more "IF" and "OR" statements. Check this one out (remove carriage returns but not commas)

=
IF( AND(ISBLANK(A1)=true,ISBLANK(B1)=true) , "bothblank",
IF( OR(ISBLANK(A1)=true,ISBLANK(B1)=true) , "atleastoneblank" , 
IF( AND(ISBLANK(A1)=false,ISBLANK(B1)=false) , "neitherblank")))

This formula looks at the two cells and tells you their status. Should be pretty straightforward. This could be used if you had some data, but not enough to make the final calculation and didn't want the zeroes or errors to mess with your further analysis. Just remove the stuff inside, leave the quote signs, and you've got a blank cell for any of the three situations. Moving further, if you want a different operation done in those three situations, remove the quote signs and insert your operation after the comma. There's probably something more sophisticated out there as well. Good luck!

share|improve this answer

Tried to find all kinds of solutions with formulas, but wasn't successful. Therefore I wrote this very small script to do the work for you:

function myCalc(range1,range2) {
  if(range1.length != range2.length) {
    throw "Both ranges need to be the same !!";
    return;
  }

  var l = range1.length;
  var output = [];

  for(i=0; i<l; i++) {
    if(range1[i] == 0 || range1[0] == "" ||
      range2[i] == 0 || range2[0] == "") {
      output.push("");
    } else {
      output.push([Number(range1[i]) - Number(range2[i])]);
    }
  }  
  return output;
}

Under Tools>Script editor you can paste the code. Press the bug button (will safe and de-bug) and you're on the go !!

share|improve this answer

You can still use nested IFs:

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

There's no need to make a script to do that :)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.