Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
"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. |
#4
|
|||
|
|||
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? |
#5
|
|||
|
|||
"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) |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What formula is used for subtracting a range of different cells f. | Excel Worksheet Functions | |||
Formula to count the cells in a range that have a fill color. | Excel Discussion (Misc queries) | |||
Excel - formula to calculate colored fill cells within a range wi. | Excel Worksheet Functions | |||
to copy a formula in cell c1 (+b1/b11) to cells c2-10, how can i . | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |