Hi Steve

I don't think anybody was calling anybody a liar. People are often
mistaken when they make an assertion which someone else believes to be
incorrect i.e. the implication is mistaken, not a liar. Still, enough of
this and every one getting hot under the collar as it serves no purpose.

In the context of what you now describe, with large numbers of files
where there may have been inconsistency of data entry in the past, the
problem as I see it is twofold.
1. How do you ensure that all future data entry is consistent
2. What is the ideal construct of your Sumproduct expressions to deal
with potentially different data constructs.

If you would like to mail me a copy of a set of past data that works,
another one of a set of past data that doesn't work and a copy of one of
your new blank sheets where you say you can create a problem, I would be
happy to take a look and make a suggestion - albeit that it will only
encapsulate parts of suggestions already made by either Harlan or
myself.

If you want to send the files direct, remove NOSPAM from my address.

--
Regards

Roger Govier


"SteveDB1" wrote in message
...
Roger,
My response in that manner was due solely to the fact he said he did
not
believe me. I've got no tolerance or patience for people who call me a
liar.
And quite frankly, I don't have time to lie about this. I'm already
too busy
to waste that kind of time.
I know that I'm not doing something right. I know that whatever it is
that I
am doing is not working. I would not be here if all I wanted to do was
to
blame someone else for my Excel woes. I've been trying to get this
routine to
work, consistently, for approx. 4 months now. I'd initially tried
using sumif
instead, but it appears to not take more than one test grouping.
However, it
did work consistently. I never had any problems with it-- of any kind.
I'm
only here because I need to solve a problem.
Now, onto the issues.
I get that we're talking data types. I get that computers look at
values
input into cells as datatypes, and put no initial property to that
data other
than what we apply to it-- gigo. I further get that he believed that
it was
something that I'd done to the data that would've caused it to resolve
into
my stated problem.
However, I did nothing other than input data into existing worksheets,
and
apply the "formula" to those datasets. I have made various
modifications to
the sheets to determine why I'd get various outputs. But that was
AFTER they
failed.
One of my colleagues, and myself have both taken blank, "unspoiled"
worksheets and tried the sumproduct formula. Each of us have gotten
different
results, resulting in an unexplained set of inconsistent
behaviours/results.
As mentioned in my last post to Harlan, I took a blank worksheet, and
did a
sample dataset. I did not format anything.
I tried all of his "formulas" and only one worked. And that was the
one
where both the source, and test ended with the &"". The one with the
double
-- signs returned #value, as did the one with the single minus sign.
The one
where the test set had the &"", and the source set did not have it did
not
work. It returned a 0.00.
Ok, so you guys are saying that I either need to set the source, or
the test
sets to a text string, or numeric type datatype.
The problem that I'm attempting to get around is that we have over
1000
workbooks stored on our server with various sources dating back to the
late
1990's. When we get around to updating these, we have no idea where
they
originated from beyond our office. When I get them, I go through and
reset
all of the formatting to the most basic that I'm able to do, WITHOUT
having
to go through each, and every cell to do so. I.e., I'll highlight all
of the
worksheet tabs, and rightclick, left click "format cells" and set my
font to
arial, set the font-size to 12, and for specific columns, set to
number, or
text, etc... All common.
but when a column shows me (within the format cells window) that it's
set to
general, or text, or numeric, and then acts differently, that adds to
my work
load by far too much to be forced to look through each and every cell
and
force or coerce the result.
It is this that I need, and am trying to overcome.
So, now, back to the beginning.
How can I resolve this problem.
1- it must act consistently- out of an office of 10 engineers who
would have
access to these files, if I'm the only one who uses it, it's pretty
much
useless.
2- If I'm always trying to look for reasons why it's not working, then
I'm
wasting time, and money.
All I care about at this point is getting it to work-- period.
So, how can I do this.... that's all I want.
For the larger issues, I've already resolved. I.e., you've already
confirmed
that my equation is correct.
From our discussions, and the like, it still goes back to some type of
formatting. One column is set to a text datatype, and the other is a
numeric
datatype. My point is that I did not set those. All I'm doing is
entering a
number in a column, and changing a name from one value to another. I
don't go
in and reformat unless it's absolutely necessary-- it's a waste of
time.

"Roger Govier" wrote:

Hi Steve

Calm down!!!

I posted a reply to you at 17:58 on 17/11/06 in which I tried to
explain
the differences in data types and reasons why you were getting your
inconsistent results (to which you did not respond).
Harlan has more thoroughly and eloquently explained the issues and
was
in no way insulting you, I don't see why you took it that way.
All we have been trying to do is help you. None of us are employees
of
Microsoft, none of us get paid for trying to help.
It is of course your prerogative as to whether you wish to accept any
advice in respect to a problem which you have posted into the forum.

I have used Sumproduct reliably for many years in all sorts of
applications for myself and clients provided I have obeyed the simple
rules of ensuring that I am comparing similar data types. More expert
users than myself, like Harlan and many others who frequent these
newsgroups, have also used the function very successfully and
reliably.

Like Harlan, I would also be sceptical of inconsistency in outcome,
where data types are consistent and many is the time that I have
sworn
to myself that the program is behaving wrongly, only to find that it
is
a mistake on my part. We all make them, and hopefully we all learn
from
them.

As I suggested in my email, and Harlan in his, it may well have been
that the Reference numbers were copied and pasted in from another
application, and were therefore text values at source and became text
values at destination. Or, as I suggested in my posting, if the
reference numbers are greater than 15 digits, they would have to be
entered as text values (read my email again).

Leave them as Text, just format your cell A3 as Text and enter your
required lookup and all will be well. Consistency will be achieved on
both sides and your blood pressure may return to normal levels <bg.

--
Regards

Roger Govier


"SteveDB1" wrote in message
...
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.