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?