I’ve discussed MAXIF and MINIF previously, but using MIN in an array formula can be tricky. Mike provided another example that I thought was worth sharing.
To find the MAX value for Los Angeles is pretty straight forward.
For any entry in A2:A6 that is not Los Angeles, the array will contain zero so the MAX function automatically takes care of it.
The next part was the tricky part, or so it seemed. Find the MIN value for Los Angeles but only for non-zero values. For that, you need to wrap the whole array in an IF statement and convert any zeros into strings so they are ignored by the MIN function. What makes this less tricky is the fact that non-Los Angeles entries will be zero (and we want to ignore those) and zero entries will be zero (and we want to ignore those). So all the rows that we want to ignore are zero. Here’s the formula
I really got twisted around on that one, but I suppose even the failures are useful. To use my failing effort, suppose that you want to find the MIN for Los Angeles but only for values that are greater than 5. Now it’s not true that all of the rows I want to ignore will be zero, so there’s one extra step involved.
You can probably guess how I over-complicated the first MIN. Oh well, live and learn.