Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need formula to retrieve last non-blank cell in range
I'm looking for a function or formula in Excel 2003 which will return the
last non-blank cell in a range. The values in the range are numeric; however, they're not sorted in numeric order. I could probably figure out how to write a macro, but I'd prefer a formula. Any suggestions? Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need formula to retrieve last non-blank cell in range
Try one of these:
Last numeric cell in Col_A: =LOOKUP(10^99,A:A) Last non-blank cell in Col_A: =LOOKUP(2,1/(A1:A100<""),A1:A100) Does that help? *********** Regards, Ron XL2002, WinXP "Eric" wrote: I'm looking for a function or formula in Excel 2003 which will return the last non-blank cell in a range. The values in the range are numeric; however, they're not sorted in numeric order. I could probably figure out how to write a macro, but I'd prefer a formula. Any suggestions? Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need formula to retrieve last non-blank cell in range
Ron - Thank you. Yes, your suggestion does solve my problem. But honestly,
it's a bit of a mystery why. The help text for LOOKUP says that the values have to be in ascending order... but mine are not. So I don't know how it works. As to your second formula, it's even more mysterious. Why the 2 as the first parameter? Also, I don't understand the syntax. What does the "1/(A1:A100<"")" mean? The help text indicates that the second parameter must be a "lookup vector" which contains only one row or column. I don't understand Excel's formula syntax. What does Excel mean by 1 slash an expression containing a range? What am I missing? I'm not a novice at this stuff, but I don't understand why this works. Thanks for your help, and any additional explanation you can offer. Eric "Ron Coderre" wrote: Try one of these: Last numeric cell in Col_A: =LOOKUP(10^99,A:A) Last non-blank cell in Col_A: =LOOKUP(2,1/(A1:A100<""),A1:A100) Does that help? *********** Regards, Ron XL2002, WinXP "Eric" wrote: I'm looking for a function or formula in Excel 2003 which will return the last non-blank cell in a range. The values in the range are numeric; however, they're not sorted in numeric order. I could probably figure out how to write a macro, but I'd prefer a formula. Any suggestions? Thanks. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need formula to retrieve last non-blank cell in range
Regarding your questions about the LOOKUP function:
Per Exce help: "If LOOKUP can't find the lookup_value, it matches the largest value in lookup_vector that is less than or equal to lookup_value." Using that information: =LOOKUP(2,1/(A1:A100<""),A1:A100) In that formula, the second argument divides the number 1 by 1 or 0, the numeric result of whether each cell is non-blank (1) or blank (0). 1/1 returns 1. 1/0 returns #DIV/0!. Consequently, when LOOKUP cannnot locate 2 in the second array, it will return the last numeric item in the list that is less than 2, which is the last 1. That item will refer to the last non-blank cell in the third argument. =LOOKUP(10^99,A:A) The same rules apply in that formula. 10^99 is a much larger number than I would ever expect in a worksheet. When the LOOKUP function cannot find it in a list of numbers, it will return the last number in the list that is less than 10^99. Note: If you happen to be an astronomer or scientist, you may want to lookup the largest number Excel can handle: 9.99999999999999E-307 instead of 10^99 Does that help? *********** Regards, Ron XL2002, WinXP "Eric" wrote: Ron - Thank you. Yes, your suggestion does solve my problem. But honestly, it's a bit of a mystery why. The help text for LOOKUP says that the values have to be in ascending order... but mine are not. So I don't know how it works. As to your second formula, it's even more mysterious. Why the 2 as the first parameter? Also, I don't understand the syntax. What does the "1/(A1:A100<"")" mean? The help text indicates that the second parameter must be a "lookup vector" which contains only one row or column. I don't understand Excel's formula syntax. What does Excel mean by 1 slash an expression containing a range? What am I missing? I'm not a novice at this stuff, but I don't understand why this works. Thanks for your help, and any additional explanation you can offer. Eric "Ron Coderre" wrote: Try one of these: Last numeric cell in Col_A: =LOOKUP(10^99,A:A) Last non-blank cell in Col_A: =LOOKUP(2,1/(A1:A100<""),A1:A100) Does that help? *********** Regards, Ron XL2002, WinXP "Eric" wrote: I'm looking for a function or formula in Excel 2003 which will return the last non-blank cell in a range. The values in the range are numeric; however, they're not sorted in numeric order. I could probably figure out how to write a macro, but I'd prefer a formula. Any suggestions? Thanks. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need formula to retrieve last non-blank cell in range
Ron - Thank you, that does solve my problem.
I don't think there's any way to determine this from Excel help. The sentence you quote indicates that the function will match the LARGEST value that's less than or equal to the lookup value. It does not say that it will match the LAST value. It also does says that the list must be sorted in ascended order, and does not define what happens when the list is not sorted. But in any case, it's understandable that the function would work this way, even though it's undefined. It's a clever solution. Regards, Ora "Ron Coderre" wrote: Regarding your questions about the LOOKUP function: Per Exce help: "If LOOKUP can't find the lookup_value, it matches the largest value in lookup_vector that is less than or equal to lookup_value." Using that information: =LOOKUP(2,1/(A1:A100<""),A1:A100) In that formula, the second argument divides the number 1 by 1 or 0, the numeric result of whether each cell is non-blank (1) or blank (0). 1/1 returns 1. 1/0 returns #DIV/0!. Consequently, when LOOKUP cannnot locate 2 in the second array, it will return the last numeric item in the list that is less than 2, which is the last 1. That item will refer to the last non-blank cell in the third argument. =LOOKUP(10^99,A:A) The same rules apply in that formula. 10^99 is a much larger number than I would ever expect in a worksheet. When the LOOKUP function cannot find it in a list of numbers, it will return the last number in the list that is less than 10^99. Note: If you happen to be an astronomer or scientist, you may want to lookup the largest number Excel can handle: 9.99999999999999E-307 instead of 10^99 Does that help? *********** Regards, Ron XL2002, WinXP "Eric" wrote: Ron - Thank you. Yes, your suggestion does solve my problem. But honestly, it's a bit of a mystery why. The help text for LOOKUP says that the values have to be in ascending order... but mine are not. So I don't know how it works. As to your second formula, it's even more mysterious. Why the 2 as the first parameter? Also, I don't understand the syntax. What does the "1/(A1:A100<"")" mean? The help text indicates that the second parameter must be a "lookup vector" which contains only one row or column. I don't understand Excel's formula syntax. What does Excel mean by 1 slash an expression containing a range? What am I missing? I'm not a novice at this stuff, but I don't understand why this works. Thanks for your help, and any additional explanation you can offer. Eric "Ron Coderre" wrote: Try one of these: Last numeric cell in Col_A: =LOOKUP(10^99,A:A) Last non-blank cell in Col_A: =LOOKUP(2,1/(A1:A100<""),A1:A100) Does that help? *********** Regards, Ron XL2002, WinXP "Eric" wrote: I'm looking for a function or formula in Excel 2003 which will return the last non-blank cell in a range. The values in the range are numeric; however, they're not sorted in numeric order. I could probably figure out how to write a macro, but I'd prefer a formula. Any suggestions? Thanks. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need formula to retrieve last non-blank cell in range
Hi, Eric
I'm glad you liked the solution. I can't take credit for it, though; It's been around for a while. Regarding the Excel Help quote: Help goes on to mention that if the list is not sorted, the results may be unexpected. But, once you realize how it behaves in this usage...the results are definitely expected. <g *********** Regards, Ron XL2002, WinXP "Eric" wrote: Ron - Thank you, that does solve my problem. I don't think there's any way to determine this from Excel help. The sentence you quote indicates that the function will match the LARGEST value that's less than or equal to the lookup value. It does not say that it will match the LAST value. It also does says that the list must be sorted in ascended order, and does not define what happens when the list is not sorted. But in any case, it's understandable that the function would work this way, even though it's undefined. It's a clever solution. Regards, Ora "Ron Coderre" wrote: Regarding your questions about the LOOKUP function: Per Exce help: "If LOOKUP can't find the lookup_value, it matches the largest value in lookup_vector that is less than or equal to lookup_value." Using that information: =LOOKUP(2,1/(A1:A100<""),A1:A100) In that formula, the second argument divides the number 1 by 1 or 0, the numeric result of whether each cell is non-blank (1) or blank (0). 1/1 returns 1. 1/0 returns #DIV/0!. Consequently, when LOOKUP cannnot locate 2 in the second array, it will return the last numeric item in the list that is less than 2, which is the last 1. That item will refer to the last non-blank cell in the third argument. =LOOKUP(10^99,A:A) The same rules apply in that formula. 10^99 is a much larger number than I would ever expect in a worksheet. When the LOOKUP function cannot find it in a list of numbers, it will return the last number in the list that is less than 10^99. Note: If you happen to be an astronomer or scientist, you may want to lookup the largest number Excel can handle: 9.99999999999999E-307 instead of 10^99 Does that help? *********** Regards, Ron XL2002, WinXP "Eric" wrote: Ron - Thank you. Yes, your suggestion does solve my problem. But honestly, it's a bit of a mystery why. The help text for LOOKUP says that the values have to be in ascending order... but mine are not. So I don't know how it works. As to your second formula, it's even more mysterious. Why the 2 as the first parameter? Also, I don't understand the syntax. What does the "1/(A1:A100<"")" mean? The help text indicates that the second parameter must be a "lookup vector" which contains only one row or column. I don't understand Excel's formula syntax. What does Excel mean by 1 slash an expression containing a range? What am I missing? I'm not a novice at this stuff, but I don't understand why this works. Thanks for your help, and any additional explanation you can offer. Eric "Ron Coderre" wrote: Try one of these: Last numeric cell in Col_A: =LOOKUP(10^99,A:A) Last non-blank cell in Col_A: =LOOKUP(2,1/(A1:A100<""),A1:A100) Does that help? *********** Regards, Ron XL2002, WinXP "Eric" wrote: I'm looking for a function or formula in Excel 2003 which will return the last non-blank cell in a range. The values in the range are numeric; however, they're not sorted in numeric order. I could probably figure out how to write a macro, but I'd prefer a formula. Any suggestions? Thanks. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need formula to retrieve last non-blank cell in range
When you say "it's been around for awhile"... what's the location of
"around"? What I mean is, is there any compilation of these undocumented-but-true features? I suspect there's no canonical source... but if you can point me in the right direction(s) to read more about Excel tricks and techniques, maybe I can get started in this direction. Or should I just continue to use Google as my starting place for research into the lore of this secret fraternity? Thanks. Eric |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need formula to retrieve last non-blank cell in range
Eric
Regarding: what's the location of "around"? < My friend, you're already there. It's impossible for any book or website to cover every potential situation an Excel user might encounter. You could scan hundreds of websites and read thousands of pages on Excel tricks, tips, and techniques and still not find what you need. The most efficient way to get the information you need when you need it is to tap into the amazing pool of talent in these newsgroups. *********** Best Regards, Ron XL2002, WinXP "Eric" wrote: When you say "it's been around for awhile"... what's the location of "around"? What I mean is, is there any compilation of these undocumented-but-true features? I suspect there's no canonical source... but if you can point me in the right direction(s) to read more about Excel tricks and techniques, maybe I can get started in this direction. Or should I just continue to use Google as my starting place for research into the lore of this secret fraternity? Thanks. Eric |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
retrieve cell formatting with IF(HLOOKUP) formula | Excel Worksheet Functions | |||
Match function...random search? | Excel Worksheet Functions | |||
Possible Lookup Table | Excel Worksheet Functions |