Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
rolan
 
Posts: n/a
Default 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   Report Post  
Aladin Akyurek
 
Posts: n/a
Default

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   Report Post  
Harlan Grove
 
Posts: n/a
Default

"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   Report Post  
Aladin Akyurek
 
Posts: n/a
Default

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   Report Post  
Harlan Grove
 
Posts: n/a
Default

"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   Report Post  
rolan
 
Posts: n/a
Default

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   Report Post  
Tommy
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
What formula is used for subtracting a range of different cells f. tim Excel Worksheet Functions 3 April 21st 23 10:07 PM
Formula to count the cells in a range that have a fill color. Slainteva Excel Discussion (Misc queries) 2 January 19th 05 09:25 PM
Excel - formula to calculate colored fill cells within a range wi. MA Excel Worksheet Functions 1 January 7th 05 05:06 PM
to copy a formula in cell c1 (+b1/b11) to cells c2-10, how can i . bvi Excel Worksheet Functions 3 December 23rd 04 07:14 PM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM


All times are GMT +1. The time now is 07:55 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"