I'm trying to do the following:
Offset(A15, ____, -5)

where _____ is a function that says look at the range (B3:B20) and
return a reference to the first value that is greater than zero. I'd
rather not create vba code behind it, but will if I have absolutely
have to. Is there any way to see what is the first value in a range
that is greater than o?

Ok, I just found some code that works, now I just need an explanation
of how it works:
{=MATCH(1,--(RANGE0),0)}
This works perfectly, but I have no idea why. Can someone walk me
through what it's doing?

This is an array formula (entered using Shift+Ctrl+Enter).

It "looks" through each cell in the range and if it is 0, sets a value of
1 (this is what the -- does, converting TRUE/FALSE to 1/0). So we end up with
an "internal" array of 1s and 0s corresponding to cells in the range.

tMATCH then matches the "1" (as search argument) to find the first "1" (one
) in the range(internal array) and returns the position. MATCH always returns
the first occurence of the search argument.

HTH

" wrote:

Ok, I just found some code that works, now I just need an explanation
of how it works:
{=MATCH(1,--(RANGE0),0)}
This works perfectly, but I have no idea why. Can someone walk me
through what it's doing?

Fantastic. Thanks, that makes perfect sense. So if I didn't do the
--, I would have had to match on TRUE or FALSE then correct? Thanks so
much for the help.
Toppers wrote:
This is an array formula (entered using Shift+Ctrl+Enter).

It "looks" through each cell in the range and if it is 0, sets a value of
1 (this is what the -- does, converting TRUE/FALSE to 1/0). So we end up with
an "internal" array of 1s and 0s corresponding to cells in the range.

tMATCH then matches the "1" (as search argument) to find the first "1" (one
) in the range(internal array) and returns the position. MATCH always returns
the first occurence of the search argument.

HTH

" wrote:

Ok, I just found some code that works, now I just need an explanation
of how it works:
{=MATCH(1,--(RANGE0),0)}
This works perfectly, but I have no idea why. Can someone walk me
through what it's doing?

So if I didn't do the --, I would have had to
match on TRUE or FALSE then correct?

Correct

Biff

wrote in message
oups.com...
Fantastic. Thanks, that makes perfect sense. So if I didn't do the
--, I would have had to match on TRUE or FALSE then correct? Thanks so
much for the help.
Toppers wrote:
This is an array formula (entered using Shift+Ctrl+Enter).

It "looks" through each cell in the range and if it is 0, sets a value
of
1 (this is what the -- does, converting TRUE/FALSE to 1/0). So we end up
with
an "internal" array of 1s and 0s corresponding to cells in the range.

tMATCH then matches the "1" (as search argument) to find the first "1"
(one
) in the range(internal array) and returns the position. MATCH always
returns
the first occurence of the search argument.

HTH

" wrote:

Ok, I just found some code that works, now I just need an explanation
of how it works:
{=MATCH(1,--(RANGE0),0)}
This works perfectly, but I have no idea why. Can someone walk me
through what it's doing?