|
Find last nonblank in a column
How can I find the last nonblank value in a column of numbers?
TIA GWB |
Try this:
=LOOKUP(2,1/(1-ISBLANK(F1:F1000)),F1:F1000) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "George W. Barrowcliff" wrote in message m... How can I find the last nonblank value in a column of numbers? TIA GWB |
George,
Provided that you do not mean WHOLE column when you say column of numbers try: =LOOKUP(2,1/ISNUMBER(L1:L1000),L1:L1000) -- HTH Sandy Replace@mailinator with @tiscali.co.uk "George W. Barrowcliff" wrote in message m... How can I find the last nonblank value in a column of numbers? TIA GWB |
=LOOKUP(9.99999999999999E+307,A:A)
which will return the last numeric value from column A if any. George W. Barrowcliff wrote: How can I find the last nonblank value in a column of numbers? TIA GWB |
Thanks, for the quick responses. Does exactly what I needed to do.
"Sandy Mann" wrote in message ... George, Provided that you do not mean WHOLE column when you say column of numbers try: =LOOKUP(2,1/ISNUMBER(L1:L1000),L1:L1000) -- HTH Sandy Replace@mailinator with @tiscali.co.uk "George W. Barrowcliff" wrote in message m... How can I find the last nonblank value in a column of numbers? TIA GWB |
That puts efficiency in back seat. <g
George W. Barrowcliff wrote: Thanks, for the quick responses. Does exactly what I needed to do. "Sandy Mann" wrote in message ... George, Provided that you do not mean WHOLE column when you say column of numbers try: =LOOKUP(2,1/ISNUMBER(L1:L1000),L1:L1000) -- HTH Sandy Replace@mailinator with @tiscali.co.uk "George W. Barrowcliff" wrote in message . com... How can I find the last nonblank value in a column of numbers? TIA GWB -- [1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents. [2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence. |
Sandy Mann wrote:
Aladin, I accept that your use of LOOKUP is more efficient than mine, (well my plagiarised version really, probably the only original thought I have had was, "I wonder if there is anything on the net about Excel?"), for one thing 'mine' used two function calls. So that I can expand my understanding of how XL works, do you know if LOOKUP checks all 65,536 rows or just the used range? If it does check all rows, is it still faster than a limited range - say 1,000 rows. I other words is it 65 times faster? LOOKUP() effects a binary search (see: http://www.nist.gov/dads/HTML/binarySearch.html), thus very fast. In =LOOKUP(2,1/ISNUMBER(L1:L1000),L1:L1000) it's not LOOKUP itself that requires time. Rather: (a) the evaluation of ISNUMBER(L1:L1000), and (b) 1/Expression Hence, the formula =LOOKUP(2,1/ISNUMBER(L1:L1000),L1:L1000) is significantly less efficient compared to =LOOKUP(9.99999999999999E+307,L:L) When the interest is in retrieving the last numeric value, one would should invoke the latter, not the former. |
"Aladin Akyurek" wrote in message
... LOOKUP() effects a binary search (see: http://www.nist.gov/dads/HTML/binarySearch.html), thus very fast. Thank you Aladin. I don't profess to understand all the information in link that you posted but I took from it that in the worst case scenario it would only take 17 comparisons to produce a result for 65536 rows which, as you said, is very fast -- Regards Sandy Replace@mailinator with @tiscali.co.uk |
Can someone please explain how this formula works =LOOKUP(2,1/ISNUMBER(L1:L1000),L1:L1000) in the context LOOKUP vector having the following syntax LOOKUP(LOOKUP_VALUE,LOOKUP_VECTOR,RESULT_VECTOR) If Column L contains data, ISNUMBER(L1:L1000) evaluates to TRUE (or 1) with the reciprocal also being 1. Parsed to the LOOKUP function, the result yields =LOOKUP(2,1,L1:L1000) - which is nothing like the conventional syntax quoted above. I am stumped. TIA -- davidm ------------------------------------------------------------------------ davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645 View this thread: http://www.excelforum.com/showthread...hreadid=389625 |
See my contrib in:
http://tinyurl.com/7ysq5 davidm wrote: Can someone please explain how this formula works =LOOKUP(2,1/ISNUMBER(L1:L1000),L1:L1000) in the context LOOKUP vector having the following syntax LOOKUP(LOOKUP_VALUE,LOOKUP_VECTOR,RESULT_VECTOR) If Column L contains data, ISNUMBER(L1:L1000) evaluates to TRUE (or 1) with the reciprocal also being 1. Parsed to the LOOKUP function, the result yields =LOOKUP(2,1,L1:L1000) - which is nothing like the conventional syntax quoted above. I am stumped. TIA |
Many thanks, Aladin. It is crystal plain now, thanks to your elaborate discourse in the link provided. I cannot cease to be amazed at some of the discoveries made about the behaviour of worksheet functions-as yet not documented! -- davidm ------------------------------------------------------------------------ davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645 View this thread: http://www.excelforum.com/showthread...hreadid=389625 |
Aladin, I sense a downside to the "Bignumber" deployment. While the formulas work flawlessly for soted data, LOOKUP seems to get into a tailspin if the data is *unsorted*. Any comments? -- davidm ------------------------------------------------------------------------ davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645 View this thread: http://www.excelforum.com/showthread...hreadid=389625 |
| All times are GMT +1. The time now is 08:25 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com