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.


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().

| improve this answer | |

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.

| improve this answer | |

Your Answer

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