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

"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.

"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

"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.

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

"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

"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

"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

[...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

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