Pointless rant about order of terms in functions

  • Thread starter Thread starter Pete McCosh
  • Start date Start date
P

Pete McCosh

Why, why, why!?
Why do different functions of the same nature not have a
standard order for their arguments? On Friday I spent
about ten minutes trying to understand why this function
wasn't working:

=Left(A1,Find(A1," ",1))

I eventually realised it was because the first two
arguments in the FIND were the wrong way round and while I
can accept that I made a pretty basic mistake, I think the
way the two functions are structured is idiotic.

Surely, if one text manipulation function has the source
string as the first argument then so should all the
others. A uniform approach would cut down stupid mistakes
like mine and make it easier to learn the general aspects
of different types of function to which the specifics can
be applied.

Exactly the same problem occurs when you use look up
functions. With INDEX, the first argument is the target
range which seems pretty logical. However, with MATCH,
the search string comes first leading to much confusion,
particularly when writing an embedded INDEX-MATCH
function. One day I'll get that right without having to
resort to the formula editor.

I presume this situation has come about as a result of
different functions being added throughout the development
cycle of Excel, but surely some thought should have been
given to a sensible, structured approach just the way you
would when creating a given workbook...
Obviously, the issue of backwards compatibility makes it
rather unlikely that I'll ever see a future release make a
wholesale rationalisation of syntax in these groups of
functions, but I can always dream.

Ah, I feel better now!
 
Pete

Glad you feel better. Next time, please post to the right newsgroup:
microsoft.public.pointlessranting.
<g>

Andy.
 
Hi Pete!

I know how you feel because I still find it difficult to remember the
ordering of arguments.

That's what I like about the function tooltips you get on formula
entry in Excel 2002 and 2003. Upgrade and your ranting days (about
this issue) will be over.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Or, type the equal sign, function name and open bracket:

=FIND(

then press Ctrl+Shift+A, to see the arguments
 
Another pointless rant: there are so many shortcuts the list isn't short and
you can't remember a single one of them, and so on and so on.

Perhaps the user friendly version issued in 3004 won't have that *?&~^!!!*
paperclip but will soberly and intelligently present the options (softkeys
or whatever) on the screen, just when you want them & not before or after.

Regards and happy ranting
 
Why, why, why!?
Click to expand...

Because, because, because!
Why do different functions of the same nature not have a
standard order for their arguments? On Friday I spent
about ten minutes trying to understand why this function
wasn't working:

=Left(A1,Find(A1," ",1))
Click to expand...

Lotus Symphony was the first spreadsheet I'm aware of that provided text
functions. 123 inherited its text functions from Symphony, and Excel copied much
of its function argument order from 123. Symphony's text functions borrowed
heavily from BASIC, but also from other environments. I'd bet the argumnt order
for FIND was based on rendering it conversationally as

find <substring> in <fullstring> starting at <position>

This is completely reversed from BASIC's InStr argument order, but I don't
recall when InStr became part of BASIC. It's possible Symphony's @FIND predates
BASIC's InStr. FWLIW, the argument order to FIND is similar to that of the Unix
utility grep. Then again, it's not unlikely the argument order simply matches
the argument order for the assembler macro used to implement the original
function in Symphony.

That said, while FIND's argument order is arguably screwed up, the fact that it
returns an error value rather than 0 or -1 when no match is found is one of the
major headaches one must live with when using any spreadsheet that perpetuates
Lotus Development Corp's poorer design decisions of +20 years ago.
Surely, if one text manipulation function has the source
string as the first argument then so should all the
others. A uniform approach would cut down stupid mistakes
like mine and make it easier to learn the general aspects
of different types of function to which the specifics can
be applied.
Click to expand...

This is a mild case. As I mentioned above, Excel's Find and VBA's InStr have
reversed argument orders (excluding InStr's optional 4th argument). Excel and
VBA have reversed precedence for exponentiation and unary minus operators. There
are several other inconsistencies between Excel and VBA. You just have to get
used to these inconsistencies in order to make use of Excel and VBA in concert.
Exactly the same problem occurs when you use look up
functions. With INDEX, the first argument is the target
range which seems pretty logical. However, with MATCH,
the search string comes first leading to much confusion,
particularly when writing an embedded INDEX-MATCH
function. One day I'll get that right without having to
resort to the formula editor.
Click to expand...

VLOOKUP and HLOOKUP came first of all the functions mentioned so far. They were
two of the major innovations in the original 123 Release 1 compared to the
version of VisiCalc current at the time. I'm pretty sure the conversational
rendering of the function call determined the argument order:

[v|h]lookup <value> in the first [column|row] of <table> and return the
corresponding value in [column|row] <n>

To me, this is very clearly the reason the lookup functions have the argument
order they do. INDEX came later (Symphony Release 1), and its argument order
seems to have been intended to be similar to that of most programming languages
for which index tokens follow the array reference token.

As for MATCH, it's more similar in functionality to [V|H]LOOKUP (and FIND) than
to INDEX, so it received [V|H]LOOKUP argument order. At least that's some
consistency.
I presume this situation has come about as a result of
different functions being added throughout the development
cycle of Excel, but surely some thought should have been
given to a sensible, structured approach just the way you
would when creating a given workbook...
Click to expand...
...

Surprise! Programming languages (and Excel formulas are a type of functional
language) evolve with all the logic and deliberate design of spoken human
languages. The most deliberately and [forced] consistently implemented language
so far produced is APL (and its offspring). For the deranged few that can think
in APL (in which company I include myself), it's very productive, almost
addictive. For the rest of humanity, it's cryptic and inflexible beyond
redemption. As a very limited example, enforced uniformity of left or right
associativity/evaluation order would drive most people to want to apply sledge
hammers to their computers even though it'd provide them with 'consistency'.

Evolution is good even if ugly proves fittest. Apparent inconsistency and
ambiguity are necessary traits of language evolution.
 
Harlan,

thanks for your informative and exhaustive post! It's
pretty interesting to see how far back some of this stuff
goes. I can also appreciate your idea of
the "conversational" syntax of some functions.

A couple of further observations:
There are several other inconsistencies between Excel and
VBA. You just have to get used to these inconsistencies
in order to make use of Excel and VBA in concert.
Click to expand...

Yes, but I don't have to like it!
Evolution is good even if ugly proves fittest. Apparent
inconsistency and ambiguity are necessary traits of
language evolution.
Click to expand...

To expand on your analogy of computer/human language. Yes
there are inconsistencies in both, but some human
languages follow much more consistent rules and grammar
than English. German, for example has very strict rules on
word order and sentence structure, e,g, the verb must
always be the second clause in a sentence. Would high
level computer languages have developed in a different way
if the early pioneers had come predominantly from this
type of background?

Cheers, Pete
 
[snip]
To expand on your analogy of computer/human language. Yes
there are inconsistencies in both, but some human
languages follow much more consistent rules and grammar
than English. German, for example has very strict rules on
word order and sentence structure, e,g, the verb must
always be the second clause in a sentence.
Click to expand...

[/OT on]
as a native speaker I would strongly disagree that German has strict
rules. Yes there're maybe more grammar rules BUT with so many
exceptions it is IMHO very difficult to learn German. e.g. how to use
'der', 'die', 'das'
[OT off]

Frank
 
Back
Top