&"" or &" " or &"~"

[Epinn]

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.
=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(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 "~"?
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

 

[Guest]

RE: =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

 

[Harlan Grove]

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

....

Don't start new threads that follow from existing threads.

=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.

....

You didn't test this very hard. Given the following in A1:A5,

foo
bar
ugh
doda
day

The formula

=LOOKUP(2,1/(COUNTIF(A1:A5,"<"&A1:A5)=1),A1:A5)

returns the INCORRECT value day, and the formula

=LOOKUP(2,1/(COUNTIF(A1:A5,"<"&A1:A5&"")=1),A1:A5)

also returns the incorrect value day, and the formula

=LOOKUP(2,1/(COUNTIF(A1:A5,"<"&A1:A5&" ")=1),A1:A5)

also returns the incorrect value day, and the formula

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

also returns the incorrect value day. They may all return the same
result, but all results are WRONG.

If you want the 'min text value', you need to use

=LOOKUP(2,1/(COUNTIF(A1:A5,"<"&A1:A5)=0),A1:A5)

which CORRECTLY returns bar. Note that you need to compare the COUNTIF
result to 0 rather than 1 *AND* you SHOULD **NOT** add any old random
garbage after the range reference in COUNTIF's 2nd arg. Such formulas
may accidentally produce the correct results (like a stopped watch
being right twice a day), but they are WRONG.

Now make A1:A5

foo
bar~
ugh
bar
day

The formula

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

happens to return the correct answer bar, but change A4 to bar~ too.
Now this formula returns #N/A. Figuring out why left as an exercise.

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.

Note that this formula compares COUNTIF to zero rather than 1.

=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?

....

Now make A1:A5

foo
ugh~
ugh
bar~
day

The formula above returns the WRONG value ugh rather than the correct
value ugh~ since "ugh~" > "ugh".

To repeat, you SHOULD **NOT** add any old random garbage after the
range reference in COUNTIF's 2nd arg. If you believe it serves any
useful purpose, you have deceived yourself.

I am going to make this more confusing for myself. . . .

The rest of us can hardly wait!

. . . Is there a connection between "~" and what Bob P. wrote
previously? . . .

This is why you should **NEVER** start @#$% new threads! What Bob
wrote previously in what @#$% thread? At least provide a url to the
article in Google Groups. You may never learn Excel, but at least try
to learn proper netiquette.

. . . If not, can someone give me an explanation similar to the
following so that I can understand "~"?

Not without having some idea what Bob might have written.

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.

....

The difference is that in COUNTIF(rng,rng) the second instance of rng
is evaluated as an array, which means that each cell in rng is
evaluated as an entry in the array, and in ambiguous situations like
this, Excel evaluates blank cells as numeric zeros. So if A1:A3
contained 3 blank cells, the array formula

=COUNTIF(A1:A3,A1:A3)

returns {0;0;0} because it evaluates the same as

=COUNTIF(A1:A3,{0;0;0})

If you enter 0 in cell A1, this formula then returns {1;1;1} because
it then still evaluates as the same thing. If you enter ="" in cell
A2, this formula then returns {1;2;1} because it then evaluates the
same as

=COUNTIF(A1:A3,{0;"";0})

However, if you use the formula

=COUNTIF(A1:A3,A1:A3&"")

it evaluates as

=COUNTIF(A1:A3,{"0";"";""})

and given the latest contents of A1:A3, it returns {1;2;2}. This is a
very good example of a thoroughly STUPID Excel design (I give it too
high a complement using the term 'design'; negligence is more to the
point): COUNTIF and SUMIF criteria are treated as text when applied to
the range in the range 1st argument, but blank cells in criteria
arguments are evaluated NUMERICALLY rather than as text. The *SMART*
(or at least competent) Excel programmer who wrote the LEN function
made it so that =LEN(someblankcell) returns 0 since some blank cell
was evaluated as "" rather than as 0 (which would have become "0", in
which case LEN would have returned 1). The *STUPID* (incompetent at
the very least) Excel programmer who wrote COUNTIF and SUMIF evidently
failed to learn from the first programmer's work. The way LEN
evaluates blank cells should have been the way COUNTIF and SUMIF
evaluate blank cells.

Anyway, &"" in =COUNTIF(rng,rng&"") forces evaluation of rng AS TEXT.
However, when you're already prepending a comparison operator in the
criteria argument, e.g., COUNTIF(rng,"<"&rng), you've ALREADY coerced
rng to text. Appending &"" does nothing (at least it does nothing!).
Appending ANYTHING ELSE **BREAKS** the formula, whether that may or
may not be apparent with minimal test data. You only need *ONE* &
operator in the criteria argument to coerce COUNTIF to evaluate its
criteria argument as text.

Learn to count to one.

 

[Harlan Grove]

RE: =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. . . .

....

OK, but if there are multiple instances of the largest text value,
this formula returns #N/A. And if there were ANY boolean values in the
range, they're be returned rather than the largest text value.


If there could be any nontext entries in the range, you have to use

=LOOKUP(2,1/(COUNTIF(A1:A5,">"&A1:A5)=0)/ISTEXT(A1:A5),A1:A5)

if you want to ensure the formula returns the CORRECT result no matter
what garbage might be present in the range.

[Does anyone else test this stuff?]

 

[Guest]

Harlan.....the formulas I posted work fine when the list contains text,
numbers, booleans, and blanks. The max formula even worked with multiples of
the max text value. You're right about multiples of the min text value,
though; it returned #N/A.

Regarding:

[Does anyone else test this stuff?]

Yes.
Is it tested with the same vigor as what I write for my company? If I have
the time, yes. If not, I do what I can, post the free solution, and address
any issues I might have missed either when they are posted or if I review my
work later.

***********
Regards,
Ron

XL2002, WinXP

RE: =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. . . .

....

OK, but if there are multiple instances of the largest text value,
this formula returns #N/A. And if there were ANY boolean values in the
range, they're be returned rather than the largest text value.


If there could be any nontext entries in the range, you have to use

=LOOKUP(2,1/(COUNTIF(A1:A5,">"&A1:A5)=0)/ISTEXT(A1:A5),A1:A5)

if you want to ensure the formula returns the CORRECT result no matter
what garbage might be present in the range.

[Does anyone else test this stuff?]

 

[Guest]

Well, maybe the same "vigor", but not always the same rigor.

***********
Regards,
Ron

XL2002, WinXP

Harlan.....the formulas I posted work fine when the list contains text,
numbers, booleans, and blanks. The max formula even worked with multiples of
the max text value. You're right about multiples of the min text value,
though; it returned #N/A.

Regarding:

[Does anyone else test this stuff?]

Yes.
Is it tested with the same vigor as what I write for my company? If I have
the time, yes. If not, I do what I can, post the free solution, and address
any issues I might have missed either when they are posted or if I review my
work later.

***********
Regards,
Ron

XL2002, WinXP

RE: =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. . . .

....

OK, but if there are multiple instances of the largest text value,
this formula returns #N/A. And if there were ANY boolean values in the
range, they're be returned rather than the largest text value.


If there could be any nontext entries in the range, you have to use

=LOOKUP(2,1/(COUNTIF(A1:A5,">"&A1:A5)=0)/ISTEXT(A1:A5),A1:A5)

if you want to ensure the formula returns the CORRECT result no matter
what garbage might be present in the range.

[Does anyone else test this stuff?]

 

[T. Valko]

The *STUPID* (incompetent at the very least)

Excel programmer who wrote COUNTIF and SUMIF....

.....must've been the same one that wrote ISBLANK and COUNTBLANK.

Biff

 

[Epinn]

Don't start new threads that follow from existing threads.

Can't please everybody. If I ask my question under the original poster's thread, I may be looked upon as "hijacking" the other's thread according to ___. In this occasion, one doesn't need the original thread to address my concern. Depending on the situation, sometimes I start a new thread and sometimes I don't.

I had already pasted what Bob wrote i.e. the paragraph which stated "Adding &"" to the end of the COUNTIF formula forces a count of the blanks........"

He didn't write the above in the thread that I referred to in my first post in this thread. It will be difficult for me to search for the thread which was from months ago. Besides, I don't think the thread was needed. I just happened to save Bob's write-up in my harddisk.

A friendly and forgiving environment may be more conducive to learning anything.

Time to stay away from the forum once again.

Epinn

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

....

Don't start new threads that follow from existing threads.

=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.

....

You didn't test this very hard. Given the following in A1:A5,

foo
bar
ugh
doda
day

The formula

=LOOKUP(2,1/(COUNTIF(A1:A5,"<"&A1:A5)=1),A1:A5)

returns the INCORRECT value day, and the formula

=LOOKUP(2,1/(COUNTIF(A1:A5,"<"&A1:A5&"")=1),A1:A5)

also returns the incorrect value day, and the formula

=LOOKUP(2,1/(COUNTIF(A1:A5,"<"&A1:A5&" ")=1),A1:A5)

also returns the incorrect value day, and the formula

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

also returns the incorrect value day. They may all return the same
result, but all results are WRONG.

If you want the 'min text value', you need to use

=LOOKUP(2,1/(COUNTIF(A1:A5,"<"&A1:A5)=0),A1:A5)

which CORRECTLY returns bar. Note that you need to compare the COUNTIF
result to 0 rather than 1 *AND* you SHOULD **NOT** add any old random
garbage after the range reference in COUNTIF's 2nd arg. Such formulas
may accidentally produce the correct results (like a stopped watch
being right twice a day), but they are WRONG.

Now make A1:A5

foo
bar~
ugh
bar
day

The formula

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

happens to return the correct answer bar, but change A4 to bar~ too.
Now this formula returns #N/A. Figuring out why left as an exercise.

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.

Note that this formula compares COUNTIF to zero rather than 1.

=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?

....

Now make A1:A5

foo
ugh~
ugh
bar~
day

The formula above returns the WRONG value ugh rather than the correct
value ugh~ since "ugh~" > "ugh".

To repeat, you SHOULD **NOT** add any old random garbage after the
range reference in COUNTIF's 2nd arg. If you believe it serves any
useful purpose, you have deceived yourself.

I am going to make this more confusing for myself. . . .

The rest of us can hardly wait!

. . . Is there a connection between "~" and what Bob P. wrote
previously? . . .

This is why you should **NEVER** start @#$% new threads! What Bob
wrote previously in what @#$% thread? At least provide a url to the
article in Google Groups. You may never learn Excel, but at least try
to learn proper netiquette.

. . . If not, can someone give me an explanation similar to the
following so that I can understand "~"?

Not without having some idea what Bob might have written.

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.

....

The difference is that in COUNTIF(rng,rng) the second instance of rng
is evaluated as an array, which means that each cell in rng is
evaluated as an entry in the array, and in ambiguous situations like
this, Excel evaluates blank cells as numeric zeros. So if A1:A3
contained 3 blank cells, the array formula

=COUNTIF(A1:A3,A1:A3)

returns {0;0;0} because it evaluates the same as

=COUNTIF(A1:A3,{0;0;0})

If you enter 0 in cell A1, this formula then returns {1;1;1} because
it then still evaluates as the same thing. If you enter ="" in cell
A2, this formula then returns {1;2;1} because it then evaluates the
same as

=COUNTIF(A1:A3,{0;"";0})

However, if you use the formula

=COUNTIF(A1:A3,A1:A3&"")

it evaluates as

=COUNTIF(A1:A3,{"0";"";""})

and given the latest contents of A1:A3, it returns {1;2;2}. This is a
very good example of a thoroughly STUPID Excel design (I give it too
high a complement using the term 'design'; negligence is more to the
point): COUNTIF and SUMIF criteria are treated as text when applied to
the range in the range 1st argument, but blank cells in criteria
arguments are evaluated NUMERICALLY rather than as text. The *SMART*
(or at least competent) Excel programmer who wrote the LEN function
made it so that =LEN(someblankcell) returns 0 since some blank cell
was evaluated as "" rather than as 0 (which would have become "0", in
which case LEN would have returned 1). The *STUPID* (incompetent at
the very least) Excel programmer who wrote COUNTIF and SUMIF evidently
failed to learn from the first programmer's work. The way LEN
evaluates blank cells should have been the way COUNTIF and SUMIF
evaluate blank cells.

Anyway, &"" in =COUNTIF(rng,rng&"") forces evaluation of rng AS TEXT.
However, when you're already prepending a comparison operator in the
criteria argument, e.g., COUNTIF(rng,"<"&rng), you've ALREADY coerced
rng to text. Appending &"" does nothing (at least it does nothing!).
Appending ANYTHING ELSE **BREAKS** the formula, whether that may or
may not be apparent with minimal test data. You only need *ONE* &
operator in the criteria argument to coerce COUNTIF to evaluate its
criteria argument as text.

Learn to count to one.

 

[Harlan Grove]

Harlan.....the formulas I posted work fine when the list contains
text, numbers, booleans, and blanks. . . .

....

Max text formula

=LOOKUP(2,1/(COUNTIF(rng,">"&rng&"~")=0),rng)

Let rng contain

c
e~
a
e
d

and this formula returns e, not e~. Change d in the last cell to
FALSE, and the wonderful formula above returns FALSE.

WRONG RESULTS MEANS THE FORMULA IS *FLAWED*!

Try it yourself. Then you can try

=LOOKUP(2,1/(COUNTIF(rng,">"&rng)=0)/ISTEXT(rng),rng)

and it'll return e~ no matter what nontext entries you make in any of
the other cells.

 

[Harlan Grove]

....must've been the same one that wrote ISBLANK and COUNTBLANK.

....

COUNTBLANK yes. ISBLANK no. ISBLANK properly distinguishes between
blank cells (cells without formula or constant contents) and cells
evaluating to zero length strings (""). Also, ISBLANK(x) always
produces the same result as (CELL("Type",x)="b"), which is exactly how
it should work. Whether it should have been named ISEMPTY rather than
ISBLANK in order to have been consistent with the equivalent Lotus 123
function or (whoda thunk?!) the name used in different language
versions of Excel itself is a different issue. I don't blame the
programmers for the function names. That credit goes to the
'designers' who can't tell a loop from an exception handler.

 

[Harlan Grove]

Can't please everybody. If I ask my question under the original
poster's thread, I may be looked upon as "hijacking" the other's
thread according to ___. In this occasion, one doesn't need the
original thread to address my concern. Depending on the situation,
sometimes I start a new thread and sometimes I don't.

....

The problem is that you referred to some formula Bob P. (did you mean
Bob Phillips? If so, be EXPLICIT) wrote without quoting that formula
or providing a url to it in Google Groups, so unless someone else had
read that article and could figure out that's the article to which you
were referring, there'd have been no way to figure out what you were
asking.

I doubt Bob Phillips wrote the formula you posted. Maybe he did, and
if so he got it wrong for the reasons I've given repeatedly in this
thread. As written (either by you or someone else) it's not robust.

 

[Guest]

Harlan,

If there's any chance that the tilde would impact results, I'd just use
CHAR(7) instead. There's effectively no chance that the ASCII Bell symbol
(007), would accidentally find its way to the cell contents.

If we look hard enough, most formulas will have some kind of weak point.
For instance, if you edit an empty cell and press alt+enter, your min text
formula returns the carriage return as the result (displayed as a box), as
does my formula. Technically, correct, but obviously not an intended entry
or desired return value, but still possible even if improbable. The key is
to catch all reasonable entries. I posted my formula, not as the ultimate
solution, but as a possible solution.

I appreciate you interest in clarifying the pitfalls inherent in many
formulas.

***********
Regards,
Ron

XL2002, WinXP

 

[Harlan Grove]

If there's any chance that the tilde would impact results, I'd
just use CHAR(7) instead. There's effectively no chance that the
ASCII Bell symbol (007), would accidentally find its way to the
cell contents.

Swell, and this wonderful formula would still choke on boolean values
in the last cell in the range. Let rng contain

c
e~
a
e
FALSE

the formula

=LOOKUP(2,1/(COUNTIF(rng,">"&rng&CHAR(7))=0),rng)

returns FALSE, not e~. Using CHAR(7) does nothing to prevent this.
YOU'RE STILL WRONG!

If we look hard enough, most formulas will have some kind of weak
point. . . .

Think so? Find a flaw in

=LOOKUP(2,1/(COUNTIF(rng,">"&rng)=0)/ISTEXT(rng),rng)

I won't hold my breath.

Maybe the formulas YOU write always have some buried flaws in them.
Me, I try to make mine robust.

 

[Guest]

Have a nice day, Harlan

 

[Epinn]

The problem is that you referred to some formula Bob P. (did you mean
Bob Phillips? If so, be EXPLICIT) wrote without quoting that formula
or providing a url to it in Google Groups...... <<

I had already addressed this in my followup post. No, I didn't refer to any formula that Bob Phillips wrote but to the paragraph "Adding &"" to the end of the COUNTIF formula forces a count ....." which I *did include* in my very first post. Subsequently, I did try to clarify in my followup post why there was no URL etc. The following (in braces) is a repeat of I wrote in the followup post.

{"I had already pasted what Bob wrote i.e. the paragraph which stated "Adding &"" to the end of the COUNTIF formula forces a count of the blanks........"

He didn't write the above in the thread that I referred to in my first post in this thread. It will be difficult for me to search for the thread which was from months ago. Besides, I don't think the thread was needed. I just happened to save Bob's write-up in my harddisk.}

I have no problem receiving "non-explosive" feedback and learning ways to improve in posting etc. I believe that teaching and learning should be fun and if it causes stress to any parties involved in the process, I rather not learn. In my opinion, respect, health and well-being are more important than learning.

I hope you will forgive me for not making the connection to the article with more belts and whistles. In e-mails, I use color to highlight the effect/connection which unfortunately I can't do with posts. I have to find ways to polish the phrasing.

Good health to you! We all appreciate your wisdom and definitely look forward to more.

In the meantime, I'll *do my best* to stay away from the forum so as not to cause any more frustration.

Epinn

Can't please everybody. If I ask my question under the original
poster's thread, I may be looked upon as "hijacking" the other's
thread according to ___. In this occasion, one doesn't need the
original thread to address my concern. Depending on the situation,
sometimes I start a new thread and sometimes I don't.

....

The problem is that you referred to some formula Bob P. (did you mean
Bob Phillips? If so, be EXPLICIT) wrote without quoting that formula
or providing a url to it in Google Groups, so unless someone else had
read that article and could figure out that's the article to which you
were referring, there'd have been no way to figure out what you were
asking.

I doubt Bob Phillips wrote the formula you posted. Maybe he did, and
if so he got it wrong for the reasons I've given repeatedly in this
thread. As written (either by you or someone else) it's not robust.