R
rolan
i want to copy the value in the last cell in a rane of data that is greater
than 0 to another cell
than 0 to another cell
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
A
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.
=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.
H
Harlan Grove
Aladin Akyurek said: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.
A
Aladin Akyurek
Harlan said: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?
H
Harlan Grove
Aladin Akyurek said:Harlan Grove wrote: ....
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)
R
rolan
thank you ... this worked
Aladin Akyurek said: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.
T
Tommy
using this formula, how do you get it to show the lowest positive number????