If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Pointless rant about order of terms in functions
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! |
#2
|
|||
|
|||
Pointless rant about order of terms in functions
Pete
Glad you feel better. Next time, please post to the right newsgroup: microsoft.public.pointlessranting. g Andy. "Pete McCosh" wrote in message ... 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! |
#4
|
|||
|
|||
Pointless rant about order of terms in functions
Or, type the equal sign, function name and open bracket:
=FIND( then press Ctrl+Shift+A, to see the arguments Norman Harker wrote: 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. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#5
|
|||
|
|||
Pointless rant about order of terms in functions
Debra,
thanks. I didn't know that feature existed. Pete -----Original Message----- Or, type the equal sign, function name and open bracket: =FIND( then press Ctrl+Shift+A, to see the arguments Norman Harker wrote: 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. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html . |
#6
|
|||
|
|||
Pointless rant about order of terms in functions
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 "Debra Dalgleish" a écrit dans le message de news: ... Or, type the equal sign, function name and open bracket: =FIND( then press Ctrl+Shift+A, to see the arguments Norman Harker wrote: 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. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#7
|
|||
|
|||
Pointless rant about order of terms in functions
"Pete McCosh" wrote...
Why, why, why!? 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)) 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. 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. 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... ... 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. -- To top-post is human, to bottom-post and snip is sublime. |
#8
|
|||
|
|||
Pointless rant about order of terms in functions
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. 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. 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 |
#9
|
|||
|
|||
Pointless rant about order of terms in functions
[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. [/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 |
Thread Tools | |
Display Modes | |
|
|