&

  • Thread starter Thread starter HGood
  • Start date Start date
H

HGood

A month or two ago some kind person here provided a formula for me. I've had
to change some things but there's a part of the formula I don't understand
and I can't find any help in my book or in Help or online.

It is (countifM4,A1:A10&""). This is only a portion of the overall formula,
but what does the & mean?

I think the "" just means blank, but how does the & tie it to the A1:A10
range?

Thanks for any help you can offer.

Harold
 
Without seeing the whole formula, it is a bit guessing, but the &
concatenates two elements, so this is adding "" to the cells A1:A10.
Normally, this is used to stop empty cells causing the formula to fail.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Bob Phillips said:
Without seeing the whole formula, it is a bit guessing, but the &
concatenates two elements, so this is adding "" to the cells A1:A10.
Normally, this is used to stop empty cells causing the formula to fail.
Click to expand...
....

And it does so by assuring that blank cells would be treated as "" rather
than as 0 (or Empty).
 
Thanks, this helps, now I need to dig in and see what it actually does in
this situation.

This is very helpful. Thanks so much.

Harold
 
Hi

Another possible explanation - it converts data, referred to in column A, to
strings. I use it myself sometimes, because Excel tends sometimes to
interprete refernces to numeral strings (in range formatted as text) as
numbers. Lately I had such a case with VLOOKUP function.


Arvi Laanemets
 
Arvi Laanemets wrote...
Another possible explanation - it converts data, referred to in column A, to
strings. I use it myself sometimes, because Excel tends sometimes to
interprete refernces to numeral strings (in range formatted as text) as
numbers. Lately I had such a case with VLOOKUP function.
Click to expand...
....

Yes, but it's a meaningless conversion *EXCEPT* for blank cells. Put 0
(number) in A1 and ="0" (numeric string) in A2. Make sure cell IV65536
is blank. Then enter the formulas

=COUNTIF(A1:A2,0)

=COUNTIF(A1:A2,"0")

=COUNTIF(A1:A2,IV65536)

Do they all return 2? On the other hand, the formula

=COUNTIF(A1:A2,IV65536&"")

should return 0.

COUNTIF's (and SUMIF's) second argument is always interpreted as a
string, BUT it appears this is only done inside the COUNTIF (and SUMIF)
functions. Excel passes these functions the second argument, but it
appears Excel converts blank cells in the second argument into numeric
zeros. This really looks like a bug that's become a 'feature' since
LEN(IV65536) returns 0 rather than 1, so there *ARE* Excel functions
that treat blank cell arguments as "" rather than 0, but the Excel
developers seem not to have applied the same type handling of COUNTIF's
(and SUMIF's) second argument as they did for LEN's argument. Want to
start a pool on how many years it'll take Microsoft to fix this?
 
Wow, this is an education into places I haven't been before, but very
helpful.

Thanks,

Harold
 
Hi


Harlan Grove said:
Arvi Laanemets wrote...
...

Yes, but it's a meaningless conversion *EXCEPT* for blank cells. Put 0
(number) in A1 and ="0" (numeric string) in A2. Make sure cell IV65536
is blank. Then enter the formulas

=COUNTIF(A1:A2,0)

=COUNTIF(A1:A2,"0")

=COUNTIF(A1:A2,IV65536)

Do they all return 2? On the other hand, the formula

=COUNTIF(A1:A2,IV65536&"")

should return 0.

COUNTIF's (and SUMIF's) second argument is always interpreted as a
string, BUT it appears this is only done inside the COUNTIF (and SUMIF)
functions. Excel passes these functions the second argument, but it
appears Excel converts blank cells in the second argument into numeric
zeros. This really looks like a bug that's become a 'feature' since
LEN(IV65536) returns 0 rather than 1, so there *ARE* Excel functions
that treat blank cell arguments as "" rather than 0, but the Excel
developers seem not to have applied the same type handling of COUNTIF's
(and SUMIF's) second argument as they did for LEN's argument. Want to
start a pool on how many years it'll take Microsoft to fix this?
Click to expand...

OK. Because the formula in state it was presented (countifM4,A1:A10&"")
didn't have any meaning, I missed that it had to be something based on
COUNTIF. So my post was about formulas in general. A couple of examples with
VLOOKUP here

A1:A3 contains numbers (format General) 1, 2, 3
B1:B3 contains charctes "A", "B", "C"
C1 (Text) contains numeric string "2"
The formula
=VLOOKUP(C1,A2:B3,2,0)
(as regular one so array formula) returns "#N/A"
{=VLOOKUP(C1,(A2:B3)&"",2,0)}
returna "B"

The same setup, but A1:A3 is formatted as Text, and C1 as General (all
values re-entered of-course)
=VLOOKUP(C1,A2:B3,2,0)
returns "#N/A" again
=VLOOKUP(C1&"",A2:B3,2,0)
(regular formula) returns "B"

Those above are of-course simplified examples. With single-type direct
entries you can avoid such problems. But sometimes those numeric strings are
results of some formulas, i.e. you can't format the cell as text. And some
entries are there texts, and some numeric strings - even when you force the
result to be string (MyFormula&""), and it behaves as string (without
alignment is left-aligned), in references it is sometimes interpreted as
number.


Arvi Laanemets
 
Arvi Laanemets wrote:
....
A1:A3 contains numbers (format General) 1, 2, 3
B1:B3 contains charctes "A", "B", "C"
C1 (Text) contains numeric string "2"
The formula
=VLOOKUP(C1,A2:B3,2,0)
(as regular one so array formula) returns "#N/A"
{=VLOOKUP(C1,(A2:B3)&"",2,0)}
returna "B"
Click to expand...
....

If there's a mix of text and numbers in the first column of the table,
a bit of data cleansing would make more sense, but it'd be much more
efficient to use the array formula

=INDEX(A2:B3,MATCH(C1&"",A2:A3&"",0),2)

Besides, forcing the second and possibly subsequent columns of the
table to be strings as well could result in incorrect return values.

However, if the first column of the table is all of the same type, then
the lookup value alone should be coerced to the type of the first
column in the table.

=VLOOKUP(--C1,A2:B3,2,0)
 
Back
Top