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:
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.
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:
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.
