Excel: Min and max of text and blanks-CodePudding

The Excel 2019 functions min(), max(), MinA(), and MaxA() don't work on non-numeric arguments. A

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?

CodePudding user response：

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.