Minimum difference if not blank

In the example shown, the goal is to calculate the minimum difference of sales minus cost, but only when both values have been entered. If either value is blank, the result should be ignored. To confirm that both values are available, the IF function is configured to use boolean logic with this expression:

(B5:B12<>"")*(C5:C12<>"")

Because each range contains 8 cells, the result of this operation is an array like this:

{1;1;1;0;1;1;0;0}

This array acts as a filter. In cases where the value is 1, IF allows values to pass through to MIN. The actual difference values are calculated with another array operation:

B5:B12-C5:C12

which generates this result:

{150;255;125;1100;150;115;-890;1025}

After the logical test is evaluated, the array passed into the MIN function looks like this:

{150;255;125;FALSE;150;115;FALSE;FALSE}

Notice that "difference value" for rows where either Sales or Cost are blank is now FALSE. The MIN function automatically ignores FALSE values and returns the minimum of remaining numbers, 115.

Maximum difference ignoring blanks

To return the maximum difference ignoring blank values, you can substitute MAX for MIN:

{=MAX(IF((B5:B12<>"")*(C5:C12<>""),B5:B12-C5:C12))}

This formula works the same way as explained above.

With MINIFS and helper column

The MINIFS function can be used to solve this problem, but it requires the use of a helper column with a formula like this:

=B5-C5

With the formula above in column D, MINIFS can be used like this:

=MINIFS(D5:D12,B5:B12,"<>",C5:C12,"<>")

This is not an array formula, and does not need to be entered with control + shift + enter.