formula to copy last positive number in range of cells

[rolan]

i want to copy the value in the last cell in a rane of data that is greater
than 0 to another cell

[Aladin Akyurek]

Assuming a single-dimension numerical range (vector) like B2:B20 or C2:N2...

=LOOKUP(2,1/(Range>0),Range)

If the numerical range is a whole column reference, say, A:A from A2 on:

=LOOKUP(2,1/(A2:INDEX(A:A,MATCH(9.99999999999999E+307,A:A))>0),A2:INDEX(A:A,MATCH(9.99999999999999E+307,A:A)))

A cell reference can be substituted for the MATCH bit if this bit is put
in a cell of its own as a formula.

rolan wrote:
> i want to copy the value in the last cell in a rane of data that is greater
> than 0 to another cell

[Harlan Grove]

"Aladin Akyurek" <[email protected]> wrote...
....
>If the numerical range is a whole column reference, say, A:A from A2 on:
>
>=LOOKUP(2,1/(A2:INDEX(A:A,MATCH(9.99999999999999E+307,A:A))>0),
>A2:INDEX(A:A,MATCH(9.99999999999999E+307,A:A)))

Since when do whole columns begin in row 2?

If A1:A2 contains {1;-1} with the rest of col A blank, this formula returns
#DIV/0!. If A1 contains 1 and A65536 contains -1 with the rest of col A
blank, this formula returns #N/A. These are desirable?

If you want to use A2:A65536, then refer to A2:A65536, *NOT* A:A. There's no
way to use entire columns in the sense of row *1* to row 65536 in LOOKUP no
matter how cleverly you believe you're constructing the range.

The point to this cleverness is to reduce the size of the 1/(x>0) term.
Also, to avoid volatile functions. In other words, to make this as
time-efficient as possible. If so, wouldn't

=IF(A65536>0,A65536,LOOKUP(2,(A1:INDEX(A1:A65535,
MATCH(9.99999999999999E307,A1:A65535))^-0.5)^-2

be more efficient? I'm assuming that since arithmetic operations take place
in the FPU, there's no difference (or negligible difference) between the
time it takes to calculate 1/x and x^-0.5.

[Aladin Akyurek]

Harlan Grove wrote:
> "Aladin Akyurek" <[email protected]> wrote...
> ...
>
>>If the numerical range is a whole column reference, say, A:A from A2 on:
>>
>>=LOOKUP(2,1/(A2:INDEX(A:A,MATCH(9.99999999999999E+307,A:A))>0),
>>A2:INDEX(A:A,MATCH(9.99999999999999E+307,A:A)))
>
>
> Since when do whole columns begin in row 2?

Meant to say: "If the numerical range is in column A from A2 on and it's
unknown where it ends, that is, a range that crimps or expands"

>
> If A1:A2 contains {1;-1} with the rest of col A blank, this formula returns
> #DIV/0!. If A1 contains 1 and A65536 contains -1 with the rest of col A
> blank, this formula returns #N/A. These are desirable?
>

Been there. Not that difficult to capture...

=LOOKUP(2,1/(A2:INDEX(A2:A65536,MATCH(9.99999999999999E+307,A2:A65536))>0),
A2:INDEX(A2:A65536,MATCH(9.99999999999999E+307,A2:A65536)))

which is one way.

Or not to repeat the MATCH bit:

F1:

=MATCH(9.99999999999999E+307,A2:A65536)

F2:

=LOOKUP(2,1/(A2:INDEX(A2:A65536,F1)>0),A2:INDEX(A2:A65536,F1))

> If you want to use A2:A65536, then refer to A2:A65536, *NOT* A:A. There's no
> way to use entire columns in the sense of row *1* to row 65536 in LOOKUP no
> matter how cleverly you believe you're constructing the range.
>

Right (if one wants to guarantee correctness, robustness, and efficiency
as I do), anyway not without additional calculations like:

G1:

=MATCH(9.99999999999999E+307,A:A)

G2:

=IF(G1>=CELL("Row",A2),LOOKUP(2,1/(A2:INDEX(A:A,G1)>0),A2:INDEX(A:A,G1)),"")

> The point to this cleverness is to reduce the size of the 1/(x>0) term.
> Also, to avoid volatile functions. In other words, to make this as
> time-efficient as possible.

That's the intent...

> If so, wouldn't
> =IF(A65536>0,A65536,LOOKUP(2,(A1:INDEX(A1:A65535,
> MATCH(9.99999999999999E307,A1:A65535))^-0.5)^-2
>
> be more efficient? I'm assuming that since arithmetic operations take place
> in the FPU, there's no difference (or negligible difference) between the
> time it takes to calculate 1/x and x^-0.5.
>

The idea is worth considering. That is, replacing 1/x with x^-0.5. Is
the formula complete as posted?

[Harlan Grove]

"Aladin Akyurek" <[email protected]> wrote...
>Harlan Grove wrote:
....
>>=IF(A65536>0,A65536,LOOKUP(2,(A1:INDEX(A1:A65535,
>>MATCH(9.99999999999999E307,A1:A65535))^-0.5)^-2
>
>The idea is worth considering. That is, replacing 1/x with x^-0.5. Is
>the formula complete as posted?

Um, no. Not correct. Try this instead.

=IF(A65536>0,A65536,LOOKUP(2,(A1:INDEX(A1:A65535,
MATCH(9.99999999999999E307,A1:A65535)))^-0.5)^-2)

[rolan]

thank you ... this worked

"Aladin Akyurek" wrote:

> Assuming a single-dimension numerical range (vector) like B2:B20 or C2:N2...
>
> =LOOKUP(2,1/(Range>0),Range)
>
> If the numerical range is a whole column reference, say, A:A from A2 on:
>
> =LOOKUP(2,1/(A2:INDEX(A:A,MATCH(9.99999999999999E+307,A:A))>0),A2:INDEX(A:A,MATCH(9.99999999999999E+307,A:A)))
>
> A cell reference can be substituted for the MATCH bit if this bit is put
> in a cell of its own as a formula.
>
> rolan wrote:
> > i want to copy the value in the last cell in a rane of data that is greater
> > than 0 to another cell
>

[Tommy]

using this formula, how do you get it to show the lowest positive number????

"Harlan Grove" wrote:

> "Aladin Akyurek" <[email protected]> wrote...
> >Harlan Grove wrote:
> ....
> >>=IF(A65536>0,A65536,LOOKUP(2,(A1:INDEX(A1:A65535,
> >>MATCH(9.99999999999999E307,A1:A65535))^-0.5)^-2
> >
> >The idea is worth considering. That is, replacing 1/x with x^-0.5. Is
> >the formula complete as posted?
>
> Um, no. Not correct. Try this instead.
>
> =IF(A65536>0,A65536,LOOKUP(2,(A1:INDEX(A1:A65535,
> MATCH(9.99999999999999E307,A1:A65535)))^-0.5)^-2)
>
>
>