A few weeks ago I wrote a Min array function to determine the minimum of a subset of items in a table. It was not the best Min function, in fact it didn’t work. I’d based it on the Max function I’d written moments earlier – a rookie mistake – and it resulted in zero when it shouldn’t have. I fixed it, but the fix was ugly. Then I realized I could use an If statement in an array formula, which helped a lot. Then I read this informative and lively post.
I’ll summarize what I learned, and propose my own Best Min Function, using the interactive workbook below.
The Workbook
The table on the left contains record high and low Fahrenheit temperatures from various cities around the world, listed by continent. (Column B contains the countries. You can unhide it.)
The formulas are in the table in the top right. They all look for the lowest of the record lows by continent.
Below the formulas are a couple of cells which trigger a couple of pitfalls of the Min function. Below that are text versions of the formulas.
The Formulas
The first formula, “multiplication,” is my original failure. The conditions (record type and continent) and the record lows are all multiplied:
(tblRecLows[Record]))}
As described in the post linked above, this only works for Min if at least one value is equal to or less than zero. Multiplying arrays of conditions like this will always yield some zeros – whenever all the conditions aren’t met – and so the minimum can never be higher than zero.
This list low temps is illustrates the problem well. For some continents the record low is negative, but for others it’s positive. You can see that this formula fails for continents where the record low is positive. For example Africa should show 30 degrees, but it shows zero.
My fix was to force all the numbers in the table to be negative, by subtracting the maximum record temperature from each number, find the minimum, and then add the maximum back. This eliminates the zero problem…
(tblRecLows[Record]-(MAX(tblRecLows[Record])+1)))+(MAX(tblRecLows[Record])+1)}
… but it’s ugly. And anyways, both multiplication versions fail if there is any stray text in your list. I noticed this because Florence had “NA’s” for both high and low records. The multiplication chokes on the text value, just as it would on 2 * “NA”, and the formula fails. You can see this by changing the “Has Text” in the “Potential Pitfalls” section to True. Both formulas yield #VALUE.
So, as faithful readers of DDOE already knew – at least if their memories are better than mine – If statements are the way to go. The “nested If” and the “Elias one-if” versions above work because they return an array filled either with temperatures that meet all the criteria, or Falses. The Min function ignores the Falses, and so returns the correct minimum.
Now for the second pitfall: Empty cells in your list are a problem because an IF statement cannot return a null. It converts blank cells to zeroes which, you guessed it, become the result of the Min function if there’s no lower number.
You can see this by changing the “Has Blanks” in the “Potential Pitfalls” section to True and following the instructions. You’ll see that the “nested if” and “Elias one-if” both fail for Africa, and will for any warm continent with a blank cell.
The proposed “Best Min” solves this problem by adding a criteria of non-blank cells. (It also eliminates the “>0” check of the multiplied conditions from Elias’s version as it seems to evaluate to the same thing.)
(tblRecLows[Record]<>"")),tblRecLows[Record]))}
There is a runner-up at the end of the list: a GetPivotData formula looking into a pivot table, or perhaps just a pivot table itself (you’ll see it if you scroll down). It might be my first choice if if didn’t require refreshing.
One last thought
I just realized that the Max function is subject to the exact same false-zero-minimum problems if all values are equal to or below zero, the inverse of the Min issue.
