matching a non-blank

J

JulieD

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
 
L

Leo Heuser

Hi Julie

One way:

=OFFSET(A1,0,MATCH(0,ISBLANK(A2:D2)+0,0)-1)

An array formula to be entered with <Shift><Ctrl><Enter>,
also if edited later.

The formula will return the contents of cell B1.
 
J

JulieD

hi Leo

thanks for this ... is there any possiblity of doing this twice in the same
range

for example

G1 = the first occurance of the non-blank (using formula provided below)
H1 = the second occurance of the non-blank

there will only ever be a maximum of 2 non-blanks (but may only be one)

Cheers
JulieD
 
L

Leo Heuser

You're welcome, Julie.

This array formula will return the last occurrence
(first one, if only one exists, second one, if two exist)
and #REF! if none exists.

=OFFSET(A1,0,MAX(IF(ISBLANK(A2:H2)+0,-1,
(COLUMN(A1:H1)-COLUMN(A1)))))
 
A

Aladin Akyurek

Also for the value associated with the last (non-error) value in the range
of interest...

=LOOKUP(REPT("z",255),IF(A2:H2<>"",A2:H2&"",A2:D2),$A$1:$H$1)

which must be confirmed with control+shift+enter.

This one will ignore error values in the target range, while sensitive to
any other data type.

If the range is empty, the result will be #N/A.
 
H

Harlan Grove

Leo Heuser said:
=OFFSET(A1,0,MATCH(0,ISBLANK(A2:D2)+0,0)-1)
Click to expand...
....

Why waste cycles converting ISBLANK's results to numbers?

=OFFSET(A1,0,MATCH(TRUE,ISBLANK(A2:D2),0)-1)
 
A

Aladin Akyurek

Typo: D2 --> H2

Aladin Akyurek said:
Also for the value associated with the last (non-error) value in the range
of interest...

=LOOKUP(REPT("z",255),IF(A2:H2<>"",A2:H2&"",A2:D2),$A$1:$H$1)

which must be confirmed with control+shift+enter.

This one will ignore error values in the target range, while sensitive to
any other data type.

If the range is empty, the result will be #N/A.
Click to expand...
 
L

Leo Heuser

Harlan Grove said:
...

Why waste cycles converting ISBLANK's results to numbers?

=OFFSET(A1,0,MATCH(TRUE,ISBLANK(A2:D2),0)-1)
Click to expand...
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>

LeoH
 
F

Frank Kabel

Leo said:
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>
Click to expand...

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
 
L

Leo Heuser

Frank Kabel said:
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
Click to expand...
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(A2:D2),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(A2:D2),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.

LeoH
 
F

Frank Kabel

Leo said:
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(A2:D2),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(A2:D2),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.
Click to expand...

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
 
H

Harlan Grove

...
...
Sorry, but you are wrong.
Click to expand...

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(A2:D2),0)-1)

and I get a name error (for TRUE).
Click to expand...
...

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(A2:D2),0)-1)
Click to expand...

[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(A2:D2),0)-1)

or

=OFFSET(A1,0,MATCH(TRUE,ISBLANK(A2:D2),0)-1)

??!
 
L

Leo Heuser

Frank Kabel said:
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
Click to expand...

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
 
H

Harlan Grove

...
...
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.
Click to expand...

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?
 
F

Frank Kabel

Leo said:
"Frank Kabel" <[email protected]> skrev i en meddelelse


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?
Click to expand...

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.
Click to expand...

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
 
L

Leo Heuser

Harlan Grove said:
...
..

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
Click to expand...
INDIRECT?

Maybe none, but that's, why I do it.

LeoH
 
L

Leo Heuser

Harlan Grove said:
...
..

Someone's wrong, but neither Frank nor me.
Click to expand...

Of course not.
If I use the formula with the Danish function names, it reads

=FORSKYDNING(A1,0,SAMMENLIGN(TRUE,ER.TOM(A2:D2),0)-1)

and I get a name error (for TRUE).
Click to expand...
..

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(A2:D2),0)-1)
Click to expand...

[tangential: doesn't XL choke if you use , as list separator rather than ;
Click to expand...
?]

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(A2:D2),0)-1)

or

=OFFSET(A1,0,MATCH(TRUE,ISBLANK(A2:D2),0)-1)

??!
Click to expand...

See my answer to Frank.

LeoH
 
L

Leo Heuser

Frank Kabel said:
Hi Leo
no, of course I don't expect this (though there's a nice Add-in to do
this formula conversion for you).


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. :)
Click to expand...

*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
 
F

Frank Kabel

[...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 :)
Click to expand...

Roger <vbg>
wish you a nice evening
Frank
 
J

JulieD

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
 
Top