I guess we could go back and forth several more times, and I'm sure that
we'll both be unswerved from our own convictions.
You do your "thing", and I'll do mine.<g>
--
Regards,
RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------
"Aladin Akyurek" <akyurek@xs4all.nl> wrote in message
news:42ffd075$0$11065$e4fe514c@news.xs4all.nl...
Ragdyer wrote:
> <<<"To clear up any misunderstanding, I'm definitely in favor of the
> comma-syntax, while you are, in fact, arguing/recommending against the
> use of SumProduct's comma-syntax.">>>
>
> No mis-understanding whatever, and you're absolutely correct in
interpreting
> my sentiments, as I correctly interpret yours.
>
Good.
> "In today's world, the use of the web for mining data is as common a fact
of
> life as is the use of the computer itself.
> They are in fact, virtually synonymous."
>
> <<"I don't think so. That was the point of my reply.">>
>
> I believe you're very wrong here, but since I'm not really in "the
> Business", I can only speak from my knowledge of my industry.
>
> We're a dying (no pun intended) breed (textile processing), along with
shoe
> manufacturing and garment manufacturing, and really can't compete with the
> Asian hemisphere of the Pacific Rim. Textile processing doesn't move
> without some connection to web based data and programs, in an attempt to
cut
> costs to the bone.
> When I started in this business in 1956, 2 to 3 times a year a price book
> and a color sample book would be mailed out gratis, to every mill in the
> country, and in those days, that numbered literally in the tens of
hundreds.
>
> Today, they are non-existent, just as computer hardware and software
> manuals.
>
Going back to "my reply," I insisteed that the comma-syntax should not
be confounded with any particular coercer. I'm not sure whether it has
anything to do with any business. Maybe are you trying to emphasize the
vagaries of "web-based input."
> <<"Issues with numeric data, erroneously typed as text (either by user or
> by the "vagaries" of the system's parser) do not constitute a valid
> reason to delegate the re-solution to functions.">>
>
> This is the crux of the discussion.
> WHAT "delegate the re-solution to functions"???
> Nobody is re-inventing the wheel!
>
> We all saw the "birth" of the SumProduct "revolution".
> It started with the asterisk!
Probably because it has been fashioned after formulas like:
{=SUM((X=x)*(Y=y)*Z)}
==>
=SUMPRODUCT((X=x)*(Y=y)*Z)
> And it did a job.
> Now, the "chick" form is the comma syntax, which *doesn't* do the same
> "good" job.
Why not, given that
=SUMPRODUCT(--(A2:A7="a"),--(B2:B7="b"),--C2:C7)
is identical to a SumProduct formula set up with the non-comma syntax;
=SUMPRODUCT((A2:A7="a")*(B2:B7="b")*C2:C7)
By the way: Results of the SumProduct formulas with non-comma syntax
won't agree with those of the SumIf formulas, applied to the same range
to sum. Hope this isn't one of those mysterious statements.
>
> In a 1,000 or 2,000 or 3,000 line sheet, who can perceive any difference
in
> "efficiency".
> And really, WHO CARES, when the insurance is really un-debatable?
I do.
>
> <<"Such concerns are better dealt with by means of separate formulas that
> audit the data. If a range should be numeric, a simple audit formula can
> verify whether that is the case.">>
>
> We talk about efficiency on one hand, and then talk about adding more
> function calls on the other.
> Why use additional formulas when one can do the job?
> Tell me that's not ludicrous!
You must be thinking of wrapping a calculation inside a complex IF.
That's not what I had in mind. An example:
B2 in Audit sheet reads 1, calculated by a formula that tests whether a
certain range, say X on sheet Y, consists of distinct items, a
requirement for further processing of X. Lets say that 1 means:
Condition is met. You have 2 choices:
(a) Check B2. If 0, take action regarding X.
(b) Wrap all formulas that process X, where appropriate, inside an IF
testing Audit!$B$2=1.
I think (a) is a sensible thing to do, while (b) can be resorted to if
it doesn't hurt efficiency too adversely.
>
> <<"The developer should provide an audit sheet (rarely done), regarding
the
> data types and the processing a spreadsheet model carries out. Auditors
> (e.g., accountants) ought to require audit sheets.">>
>
> This means absolutely nothing to an office staff whose job it is to update
> (revise, populate) the data list as often and as quickly as possible
before
> the 5:00 o'clock bell.
Maybe not. Even small and "quick and dirty" sheets would profit of
having an audit sheet.
>
> <<"A data area can consist of either user-entered values or calculated
> values. There might be good reasons for using ="" or any other
> text-value. Such an area becomes unprocessible by your suggestion.">>
>
> Cosmetics aside, =0 is just as acceptable in most cases.
>
If the issue is with erroneously text-type data and you're confident
about the error, the comma-syntax does the job too (See above).
> "If you're told something's wrong, you can look for it!"
>
> <<"Quite so. One would be well-advised to inspect the results of
> judiciously set up audit formulas.">>
>
> Again, we address the need of additional function calls to check on
> something that really *needs no* checking!
>
When that confident and you need a SumProduct formula...
=SUMPRODUCT(--(X=x),--(Y=y),...,--Z)
or, if you dislike double negation...
=SUMPRODUCT((X=x)+0,(Y=y)+0,...,Z+0)
Note that, if SumProduct would coerce truth values implicitly, you'd have:
=SUMPRODUCT((X=x),(Y=y),...,Z+0)
--
[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.
Bookmarks