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:
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 example uses the following named ranges : "color" = B6:B14, "item" = C6:C14, and "price" = E6:E14. In the example, we have pricing on items in various regions. The goal is to find the minimum price for a given color and item. This formula uses...
This example uses the following named ranges : "color" = B6:B14, "item" = C6:C14, and "price" = E6:E14. The goal is to find the maximum price for a given color and item. This formula uses two nested IF functions, wrapped inside MAX to return the...
The IF function runs a logical test and returns one value for a TRUE result, and another for a FALSE result. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions. The IF...
The Excel MINIFS function returns the smallest numeric value that meets one or more criteria in a range of values. MINIFS can be used with criteria based on dates, numbers, text, and other conditions. MINIFS supports logical operators (>,...
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.