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 |
#11
|
|||
|
|||
matching a non-blank
Leo Heuser wrote: "Frank Kabel" skrev i en meddelelse ... Leo Heuser wrote: "Harlan Grove" skrev i en meddelelse ... "Leo Heuser" wrote... ... =OFFSET(A1,0,MATCH(0,ISBLANK(A22)+0,0)-1) ... Why waste cycles converting ISBLANK's results to numbers? =OFFSET(A1,0,MATCH(TRUE,ISBLANK(A22),0)-1) To make it independent of localized names for TRUE. For the same reason I use 0 instead of FALSE in VLOOKUP(), HLOOKUP() and MATCH(). The cycles are, as you put it, wasted, that is, no one will ever know, that they were there at all g Hi Leo in this case no need for this as excel converts TRUE/FALSE automatically to their local substitutes. So though I hate to admit, Harlan is right with these enormous waste of cycles vbg Frank Hi Frank Sorry, but you are wrong. If I use the formula with the Danish function names, it reads =FORSKYDNING(A1,0,SAMMENLIGN(TRUE,ER.TOM(A22),0)-1) and I get a name error (for TRUE). I have to use the Danish equivalent for TRUE, which is SAND =FORSKYDNING(A1,0,SAMMENLIGN(SAND,ER.TOM(A22),0)-1) What *are* converted automatically are the TRUEs/FALSEs in the array, and that's precisely why you have to use the localized name for TRUE/FALSE, and that's why I use 1 and 0 instead. I doubt, that the German edition, is different, but I may be wrong. Hi Leo if I use a function like =MATCH(TRUE,....) in my english Excel version at work and open this workbook with my Germany Excel version at home also the TRUE parts automatically is converted by Excel to 'WAHR' in my case and vice versa Frank |
#12
|
|||
|
|||
matching a non-blank
"Leo Heuser" wrote...
... Sorry, but you are wrong. Someone's wrong, but neither Frank nor me. If I use the formula with the Danish function names, it reads =FORSKYDNING(A1,0,SAMMENLIGN(TRUE,ER.TOM(A22),0 )-1) and I get a name error (for TRUE). ... Of course you do! Just like you'd get an error if you used OFFSET, MATCH or ISBLANK rather than their Danish counterpart tokens in your Danish version. Excel does *NOT* translate English tokens into their Danish counterparts when entered in formulas in the Danish version. However, if you enter the formula =FORSKYDNING(A1,0,SAMMENLIGN(SAND,ER.TOM(A22),0 )-1) [tangential: doesn't XL choke if you use , as list separator rather than ; ?] in your Danish version, save the file, then load it into an English version of Excel, do you see =OFFSET(A1,0,MATCH(SAND,ISBLANK(A22),0)-1) or =OFFSET(A1,0,MATCH(TRUE,ISBLANK(A22),0)-1) ??! -- To top-post is human, to bottom-post and snip is sublime. |
#13
|
|||
|
|||
matching a non-blank
"Frank Kabel" skrev i en meddelelse
... Leo Heuser wrote: "Frank Kabel" skrev i en meddelelse ... Leo Heuser wrote: "Harlan Grove" skrev i en meddelelse ... "Leo Heuser" wrote... ... =OFFSET(A1,0,MATCH(0,ISBLANK(A22)+0,0)-1) ... Why waste cycles converting ISBLANK's results to numbers? =OFFSET(A1,0,MATCH(TRUE,ISBLANK(A22),0)-1) To make it independent of localized names for TRUE. For the same reason I use 0 instead of FALSE in VLOOKUP(), HLOOKUP() and MATCH(). The cycles are, as you put it, wasted, that is, no one will ever know, that they were there at all g Hi Leo in this case no need for this as excel converts TRUE/FALSE automatically to their local substitutes. So though I hate to admit, Harlan is right with these enormous waste of cycles vbg Frank Hi Frank Sorry, but you are wrong. If I use the formula with the Danish function names, it reads =FORSKYDNING(A1,0,SAMMENLIGN(TRUE,ER.TOM(A22),0)-1) and I get a name error (for TRUE). I have to use the Danish equivalent for TRUE, which is SAND =FORSKYDNING(A1,0,SAMMENLIGN(SAND,ER.TOM(A22),0)-1) What *are* converted automatically are the TRUEs/FALSEs in the array, and that's precisely why you have to use the localized name for TRUE/FALSE, and that's why I use 1 and 0 instead. I doubt, that the German edition, is different, but I may be wrong. Hi Leo if I use a function like =MATCH(TRUE,....) in my english Excel version at work and open this workbook with my Germany Excel version at home also the TRUE parts automatically is converted by Excel to 'WAHR' in my case and vice versa Frank Now come on, Frank! Of course it's converted, and you seriously believe, that the common user has two versions of Excel, an English one and a localized one, so when she sees my formula, it's entered and saved on the English one and then opened on the localized version? I write my formulae with the English function names, and expect users to translate them to their localized equivalents, before they are entered on their sheet. I guess, that's what we all are expecting in the English groups. What I'm saying is, that using 1 and 0 in the above mentioned situations, makes it unnecessary to know the localized names for TRUE and FALSE. LeoH |
#14
|
|||
|
|||
matching a non-blank
"Leo Heuser" wrote...
... What I'm saying is, that using 1 and 0 in the above mentioned situations, makes it unnecessary to know the localized names for TRUE and FALSE. And what benefits does this provide? Is it not likely that non-English version users reading the English newsgroups are far more likely to know how to translate TRUE and FALSE into their own language than they are OFFSET, MATCH, ISBLANK, not to mention the more esoteric functions like DEVSQ and INDIRECT? -- To top-post is human, to bottom-post and snip is sublime. |
#15
|
|||
|
|||
matching a non-blank
Leo Heuser wrote:
"Frank Kabel" skrev i en meddelelse ... [....] Hi Leo if I use a function like =MATCH(TRUE,....) in my english Excel version at work and open this workbook with my Germany Excel version at home also the TRUE parts automatically is converted by Excel to 'WAHR' in my case and vice versa Frank Now come on, Frank! Of course it's converted, and you seriously believe, that the common user has two versions of Excel, an English one and a localized one, so when she sees my formula, it's entered and saved on the English one and then opened on the localized version? Hi Leo no, of course I don't expect this (though there's a nice Add-in to do this formula conversion for you). I write my formulae with the English function names, and expect users to translate them to their localized equivalents, before they are entered on their sheet. I guess, that's what we all are expecting in the English groups. What I'm saying is, that using 1 and 0 in the above mentioned situations, makes it unnecessary to know the localized names for TRUE and FALSE. Understood, this saves this conversions. And of course I also use 0/1 (for saving characters). But we were talking about saving some cycles with the direct usage of 'TRUE' instead of '1' at the beginning of this thread for this SPECIFIC formula. :-) So my point just was, that there's no need to use 0/1 due to different Excel language versions if one is concerned that your file is opened in a different language 8like you have to deal with ATP functions for example) Best regards Frank |
#16
|
|||
|
|||
matching a non-blank
"Harlan Grove" skrev i en meddelelse
... "Leo Heuser" wrote... .. What I'm saying is, that using 1 and 0 in the above mentioned situations, makes it unnecessary to know the localized names for TRUE and FALSE. And what benefits does this provide? Is it not likely that non-English version users reading the English newsgroups are far more likely to know how to translate TRUE and FALSE into their own language than they are OFFSET, MATCH, ISBLANK, not to mention the more esoteric functions like DEVSQ and INDIRECT? Maybe none, but that's, why I do it. LeoH |
#17
|
|||
|
|||
matching a non-blank
"Harlan Grove" skrev i en meddelelse
... "Leo Heuser" wrote... .. Sorry, but you are wrong. Someone's wrong, but neither Frank nor me. Of course not. If I use the formula with the Danish function names, it reads =FORSKYDNING(A1,0,SAMMENLIGN(TRUE,ER.TOM(A22),0 )-1) and I get a name error (for TRUE). .. Of course you do! Just like you'd get an error if you used OFFSET, MATCH or ISBLANK rather than their Danish counterpart tokens in your Danish version. Excel does *NOT* translate English tokens into their Danish counterparts when entered in formulas in the Danish version. However, if you enter the formula =FORSKYDNING(A1,0,SAMMENLIGN(SAND,ER.TOM(A22),0 )-1) [tangential: doesn't XL choke if you use , as list separator rather than ; ?] Yes, it does, but I thought, that it wasn't relevant here, so I just entered the Danish function names in the original formula. in your Danish version, save the file, then load it into an English version of Excel, do you see =OFFSET(A1,0,MATCH(SAND,ISBLANK(A22),0)-1) or =OFFSET(A1,0,MATCH(TRUE,ISBLANK(A22),0)-1) ??! See my answer to Frank. LeoH |
#18
|
|||
|
|||
matching a non-blank
"Frank Kabel" skrev i en meddelelse ... Leo Heuser wrote: "Frank Kabel" skrev i en meddelelse ... [....] Hi Leo if I use a function like =MATCH(TRUE,....) in my english Excel version at work and open this workbook with my Germany Excel version at home also the TRUE parts automatically is converted by Excel to 'WAHR' in my case and vice versa Frank Now come on, Frank! Of course it's converted, and you seriously believe, that the common user has two versions of Excel, an English one and a localized one, so when she sees my formula, it's entered and saved on the English one and then opened on the localized version? Hi Leo no, of course I don't expect this (though there's a nice Add-in to do this formula conversion for you). I write my formulae with the English function names, and expect users to translate them to their localized equivalents, before they are entered on their sheet. I guess, that's what we all are expecting in the English groups. What I'm saying is, that using 1 and 0 in the above mentioned situations, makes it unnecessary to know the localized names for TRUE and FALSE. Understood, this saves this conversions. And of course I also use 0/1 (for saving characters). But we were talking about saving some cycles with the direct usage of 'TRUE' instead of '1' at the beginning of this thread for this SPECIFIC formula. :-) *We* were not talking cycles. Harlan and you were. *I* answered a specific question about, why I use 1 instead of TRUE (actually 0 instead of FALSE). I may change that later, but that has no relevance, for what I'm doing now. Over and out :-) LeoH |
#19
|
|||
|
|||
matching a non-blank
[...9
*We* were not talking cycles. Harlan and you were. *I* answered a specific question about, why I use 1 instead of TRUE (actually 0 instead of FALSE). I may change that later, but that has no relevance, for what I'm doing now. Over and out :-) Roger vbg wish you a nice evening Frank |
#20
|
|||
|
|||
matching a non-blank
wow .. talk about a thread! ... thanks for all the help guys
(the true / false / 1 / 0 / danish / german / english part of the discussion is way over my head ...) i've got enough to solve my problem ... and as i'm only looking at a couple of equations in a very small workbook i'm not too worried about "extra cycles"! Cheers JulieD "JulieD" wrote in message ... i need to find the cell in a (single row) range that is not blank and return the cell reference of the cell one row above e.g. a b c d 1 jan feb mar apr 2 xxx would give me b1 - which i then need to use in another formula however, the data in row 2 is not constant, ie can be numbers or text which changes (via database query). How can i do this? Cheers JulieD |
|
Thread Tools | |
Display Modes | |
|
|