Search code examples
exceltextmaxmin

Excel: Min and max of text and blanks

The Excel 2019 functions min(), max(), MinA(), and MaxA() don't work on non-numeric arguments. A StackOverflow answer gave a formula for performing the max and min operations on text values:

{=index(A2:A6, match(0, CountIf(A2:A6, ">" & A2:A6), 0))}

for max, with min being the same with ">" changed to "<", and where {...} means an array formula entered with Ctrl-Shft-Enter.

This works well, but I've found a strange behavior in it. If the given range includes blank cells (i.e., empty, i.e., containing nothing) at the end of the range or inside the range, it works fine, but if it includes blanks at the beginning of the range, the formula returns 0:

Excel min max on text with blanks

To complicate matters further, if the blank cells are replaced with empty strings, ="", then the above behavior is the same, except that the one with the empty string at the beginning becomes empty instead of 0.

What is going on here? Why does this formula work with blanks or empty strings at the end or inside the range, but not at the beginning?

Solution

  • This is a disadvantage of COUNTIF. While =A1>B1 will get FALSE and =B1>A1 will get TRUE because an empty cell is not greater than A but A is greater than empty, in COUNTIF blank ("") or empty will never be count. If you do =COUNTIF(A1:H1,">"&A1) you get 0. So nothing counts as to be greater than the empty cell A1.

    This is because of the usage of text concatenation in COUNTIF. =COUNTIF(A1:H1,">"&A1) will become =COUNTIF(A1:H1,">") which counts how much values are greater than nothing. That counts 0.

    In array context the COUNTIF(A1:H1,">"&A1:H1) gets {0,5,0,4,3,2,0,1} and so MATCH(0;{0,5,0,4,3,2,0,1},0) gets 1. Then INDEX(A1:H1,1) gets A1 which is empty. So the Formula shows 0 like =A1 also would do.

    You would see this when using Evaluate Formula in Excel.

    One could append a space behind the seach criteria in COUNTIF. So empty or blank would be handled like a space. And to each other cell value simply a space gets appended, what not should be problematic here.

    {=INDEX(A1:H1,MATCH(0,COUNTIF(A1:H1,">"&A1:H1&" "),0))}

    should work.

    Since COUNTIF is able using asterisk (*) used as the wildcard character to match any character, we also could append * instead of the space.

    {=INDEX(A1:H1,MATCH(0,COUNTIF(A1:H1,">"&A1:H1&"*"),0))}

    Simply try what fits better.