=LOOKUP(2,1/(COUNTIF(A1:A5,""&A1:A5&"~")=0),A1:A5)
when used to return the largest text value in a list.

The odd character (~) prevents the formula from erroneously counting blanks
as the largest value. In the COUNTIF function, as used in the formula,
blanks would return zero and would cause a tie between the largest non-blank
item and blanks. With the special character, blanks return the count of
non-blank items...leaving the largest text value to return zero.

It doesn't matter which character you use. I chose the tilde (~) for the
same kind of reason that we us the dbl-neg to coerce numeric text to
numbers......to flag the character as having a special purpose.

Try entering blank cells in the list and experiment with the the appended
character in the formula. I believe any character, excepting an empty
string(""), returns the correct value. The empty string causes the formula
to return zero.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Epinn" wrote:

I read a thread and got lost so I decide to start my own thread.

I have seen &"" quite a bit (e.g. in a COUNTIF or MATCH formula) but I have never seen &"~" until now. Some of you may be aware that I like to experiment a lot. So, here I go again.

Min text value:
=LOOKUP(2,1/(COUNTIF(A1:A5,"<"&A1:A5&"~")=1),A1:A5)

I took out the ~ and use a space instead and I got the same correct result.
I took out the ~ and did NOT leave a space i.e. "" and I got the *second* smallest value and not the min. I am surprised that this doesn't work as originally we have "" (w/o space) in the following formula.

=LOOKUP(2,1/((COUNTIF(rng,"<"&rng)=0)*(rng<"")),rng)

Max text value:
=LOOKUP(2,1/(COUNTIF(A1:A5,""&A1:A5&"~")=0),A1:A5)

Regardless of whether I use "~", " " (with space) or "" (w/o space), I got the same correct result (i.e. the max). Why does "" work for max and not min?

I am going to make this more confusing for myself. Is there a connection between "~" and what Bob P. wrote previously? If not, can someone give me an explanation similar to the following so that I can understand "~"?

Adding &"" to the end of the COUNTIF formula forces a count
of the blanks. This addition on its own removes the #DIV/0! error, but will
cause the blanks to be counted as a unique item. A further addition to the formula
resolves this by testing for those blanks. Instead of dividing the array of
counts into 1 each time, adding the test creates an array of TRUE/FALSE
values to be divided by the equivalent element in the counts array. Each
blank will resolve to FALSE in the dividend array, and the count of the
blanks in the divisor array. The result of this will be 0, so the blanks do
not get counted. <<

I appreciate all the help I can get as I am very lost now.

Epinn