formula to copy last positive number in range of cells
 

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

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

=LOOKUP(2,1/(Range0),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

"Aladin Akyurek" 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/(x0) term.
Also, to avoid volatile functions. In other words, to make this as
time-efficient as possible. If so, wouldn't

=IF(A655360,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.

Harlan Grove wrote:
"Aladin Akyurek" 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/(x0) 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(A655360,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?

"Aladin Akyurek" wrote...
Harlan Grove wrote:
....
=IF(A655360,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(A655360,A65536,LOOKUP(2,(A1:INDEX(A1:A65535,
MATCH(9.99999999999999E307,A1:A65535)))^-0.5)^-2)

thank you ... this worked

"Aladin Akyurek" wrote:

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

=LOOKUP(2,1/(Range0),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

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

"Harlan Grove" wrote:

"Aladin Akyurek" wrote...
Harlan Grove wrote:
....
=IF(A655360,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(A655360,A65536,LOOKUP(2,(A1:INDEX(A1:A65535,
MATCH(9.99999999999999E307,A1:A65535)))^-0.5)^-2)