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. Because each range contains 8 cells, the result of this operation is an array like this: 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: which generates this result: After the logical test is evaluated, the array passed into the MIN function looks like this: 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: With the formula above in column D, MINIFS can be used like this: This is not an array formula, and does not need to be entered with control + shift + enter.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
