Minimum difference if not blank

To calculate the minimum difference between two sets of values, ignoring cases where either value is blank, you can use an array formula based on the MIN and IF functions. In the example shown, the formula in F4 is:

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

which returns 115, the minimum of sales-cost, ignoring cases where either value is blank.

Note: this is an array formula and must be entered with Control + Shift + Enter.

How this formula works

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.