I am writing a formula into google sheets and I do not want the formula to register a blank cell as zero. The formula is:


It keeps giving me a zero value because some of the cells are blank. It is a monthly sales spreadsheet, so I don't have all of the data until the month is finished, but I'd like to have it working until I enter in all of the information.

migrated from superuser.com Jul 19 '16 at 9:02

This question came from our site for computer enthusiasts and power users.


Then you need to add criteria to exclude those in Columns D and C that are blank:

=ARRAYFORMULA(MIN(IF((A:A=H12)*(D:D<>"")*(C:C<>""), (D:D-C:C))))

The * acts like AND. So all three need to resolve to TRUE for it to consider the array in the MIN().

=ARRAYFORMULA(MIN(IF(A:A=H12,IF(D:D="","",IF(C:C="","",D:D-C:C)))),  the quotations directly after the D in the formula instruct to look for a blank value in column D first. The comma signifies next instructions on what to return if the logic test is true (if it finds a blank) the next quotations is the value blank desired to be filled in if so. This step is repeated for column C and finally is the instructions of what to do if either is void of blanks. A IFS function could have been used but I felt using individual IF functions better demonstrated the concept of the logic test.  
  • While the formula could do what the OP is looking for, the answers should include a brief description and explanation about how it answers the question. Ref. How to Answer – Rubén Jun 7 at 18:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy