Well, I'll tell you what Harlan, if you don't want to believe me, I guess
that's your own problem. But to satisfy your obvious ignorance, give me your
email-- one you feel safe with-- and I'll put together a sample worksheet
from a blank workbook, and email it to you. This way you'll have that "live
example" you mentioned.
I've got better things to do than be insulted by someone who obviously has
no understanding of what I'm trying to accomplish.
This was suppose to be a simple, straightforward question to what's
obviously not so simple a problem.
That MS is, or would be unwilling to resolve an issue that affects all of
their customers is what has cost them the "open source" people to begin with.
That you'd act as if I'd insulted your integrity by saying that the example
you'd given did not work tells me that you've taken this far too personally,
when it has/had absolutely nothing to do with you is equally disappointing.
I will however not let this go, and expect a more patient person than you to
deal with this issue.
There is nothing wrong with the dataset that is being used. The other
engineer that I'd initially mentioned in my first post to Roger and myself
have both opened completely blank workbooks to run our own tests on this and
have found glaring inconsistencies-- it will work with one row, but not the
next. It then may work with a few rows, and then not work with others.
He's tried using it on basketball, baseball, and other sports, and
experiecned the same exact issues. The help file in Excel said it's supposed
to work with upwards of 30 distinct--equally dimensioned-- arrays.

All we're seeking is a consistently acting "formula." That you'd pull a
semantics response is pathetic. Formulas, equations, and algorithms are all
the same thing-- a mathematical statement designed to elicit an answer. At
least have the decency to answer my question instead of playing language
games with me.
So, as I said, since you believe yourself to be infallible, send me an email
address and I'll get you a workbook with a sample dataset, and the
equations/formulas/algorithms.


"Harlan Grove" wrote:

SteveDB1 wrote...
Ok, Harlan,
I tried the last two equations you mentioned, and the first one does not
work. It only returns a zero.
(--Map!$E$4:$E$30=$A3)

or

(Map!$E$4:$E$30=$A3&"")

Simply put, I don't believe you. If you can get the result you claim to
want using formulas like =1*Map!E4 in a different range of 27 rows by 1
column and using that range rather than Map!E4:E30 in your SUMPRODUCT
formula, then Excel would give the same result using --Map!E4:E30 in
the SUMPRODUCT formula. I've NEVER seen an exception to that, and I'm
going to be skeptical about claims otherwise until I see a live
example.

Maybe you meant that Map!E4:E30 is numeric but A3 isn't. In that case
use

(Map!$E$4:$E$30=--$A3)

or

(Map!$E$4:$E$30&""=$A3)

But to be as generic as possible, use

(-Map!$E$4:$E$30=-$A3)

or

(Map!$E$4:$E$30&""=$A3&"")

So, all of this still brings me back to the point of-- how do I,
consistently, constantly, get it to work, without having to go through and
find out why one equation works and the very next one does not work.

There's NOTHING wrong with Excel's -- double operator or &"" or
SUMPRODUCT. The problem lies entirely in probable data type mismatches
between your ranges Map!E4:E30 and A3. It's entirely and exclusively up
to YOU to ensure that you're comparing text to text or numbers to
numbers.

My whole point for coming in here to ask is that the equation is not
consistently working, and I'm spending far too much time going through
looking for small, inconspicuous reasons for its failure. I came looking to
learn more about this equation because it did once work, and it sped up my
work process by an undefinable amount of time. It's now taking 3 x's longer
to figure out why it's not working.

It's not an 'equation'. It's a formula.

If your formulas appear to behave inconsistently, far & away the most
likely answer is that there are problems with YOUR data. To repeat,
it's up to YOU to ensure that you're comparing text to text or numbers
to numbers. There are ways to do that: --x is guaranteed to convert
text representations of numbers to their numeric values (with possible
floating point rounding/truncation error) while leaving numeric values
unchanged, and x&"" is guaranteed to convert numeric values to their
text representations while leaving text as-is. You could also have
trailing nonbreaking HTML spaces, and Roger Govier has already shown
how to handle those.

You're saying that one column could be being recognized as text strings,
when the other column its comparing to could be being recognized as numeric
values, and as such it returns a false because the text string is not
recognized the same as a numeric value.

Correct.

This then takes me back to the point of WHY?

Because that's how Excel works. Available data types include numbers
(usually double precision floating point), text, boolean and error
values. A value in one data type NEVER equals a value in another data
type even if they share an identical text representation.

There are some programming languages in which this isn't the case. VBA,
for instance, in which 1234# = "1234" returns TRUE. But that's because
VBA and those other languages apply implicit type conversion even to
operands of comparison operators. Excel doesn't. Whether it should or
not is a different matter, and purely academic because Microsoft is
extremely unlikely to change this behavior lest it break other people's
existing formulas.

I don't care what the value is. I don't care that 12345 is numeric in one
cell, and a text string in the other column. I just want it to be recognized
as being the same, because regardless of the format/properties behind each,
it is still just 12345.

There you're wrong. It's text in one cell and numeric in the other. You
may not appreciate the difference, and it's clear you don't want to
have to do so, but if you want to create reliable Excel formulas you're
going to have to break down and learn this distinction. To repeat from
above, this behavior is unlikely to change. Your choices are either to
learn this and accommodate how Excel works or use something other than
Excel.

I entered it as JUST 12345. I did not enter it as "12345" in one column, and
12345 in the other. Nor did I enter it as <12345, etc... ad infinitum. Which
seems to me to be strictly a background/behind-the-scenes format issue.

OK, but if one cell was formatted as Text while the other was formatted
as General, then the first will be STORED as text and the other as
numeric.

At this point I have to say I suspect you imported the list of permit
numbers from some other system. If so, it's likely that other system
stored the permit numbers as text, and Excel is respecting that other
system's data types.

So, going back to my original point--
1- is there anyway to consistently get it to work one way, without having to
spend hours looking at why it doesn't?
....

Only by always coercing both sides of your = or < comparisons to
numeric or text.

3- if no one here knows the answers that I seek, who can I speak with that
will get me the correct way to handle this?

No one who knows more about Excel that I do will give you a different
answer than I have. To repeat, this is just how Excel works, and it's
unlikely to change.