0

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:

=ARRAYFORMULA(MIN(IF(A:A=H12, (D:D-C:C))))

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.

1

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

0
=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.

Your Answer

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