|
Is there a formula to select the last non-blank cell in a list
Im trying to select the last date in each of about 1000 seperate 50 row lists
in a worksheet using a single cell at the top. Is there a formula that can do this? thank you, barry |
Is there a formula to select the last non-blank cell in a list
Try this:
=LOOKUP(1E100,A2:A100) Format as DATE -- Biff Microsoft Excel MVP "BROCK8292" wrote in message ... Im trying to select the last date in each of about 1000 seperate 50 row lists in a worksheet using a single cell at the top. Is there a formula that can do this? thank you, barry |
Is there a formula to select the last non-blank cell in a list
thats incredible, thank you very much
im sure youre busy but if you get a chance i would love to know why that works br "T. Valko" wrote: Try this: =LOOKUP(1E100,A2:A100) Format as DATE -- Biff Microsoft Excel MVP "BROCK8292" wrote in message ... Im trying to select the last date in each of about 1000 seperate 50 row lists in a worksheet using a single cell at the top. Is there a formula that can do this? thank you, barry |
Is there a formula to select the last non-blank cell in a list
The formula tells Excel to find the number 1e100 in the range A2:A100. 1e100
is a very large number (10 to the 100th power). As there is no number that large in the range, Excel returns the largest number it finds. Regards, Fred. "BROCK8292" wrote in message ... thats incredible, thank you very much im sure youre busy but if you get a chance i would love to know why that works br "T. Valko" wrote: Try this: =LOOKUP(1E100,A2:A100) Format as DATE -- Biff Microsoft Excel MVP "BROCK8292" wrote in message ... Im trying to select the last date in each of about 1000 seperate 50 row lists in a worksheet using a single cell at the top. Is there a formula that can do this? thank you, barry |
Is there a formula to select the last non-blank cell in a list
It returns the last number in that range--not the largest number in the range.
Fred Smith wrote: The formula tells Excel to find the number 1e100 in the range A2:A100. 1e100 is a very large number (10 to the 100th power). As there is no number that large in the range, Excel returns the largest number it finds. Regards, Fred. "BROCK8292" wrote in message ... thats incredible, thank you very much im sure youre busy but if you get a chance i would love to know why that works br "T. Valko" wrote: Try this: =LOOKUP(1E100,A2:A100) Format as DATE -- Biff Microsoft Excel MVP "BROCK8292" wrote in message ... Im trying to select the last date in each of about 1000 seperate 50 row lists in a worksheet using a single cell at the top. Is there a formula that can do this? thank you, barry -- Dave Peterson |
Is there a formula to select the last non-blank cell in a list
There's an error in your explanation:
With A1: 2 A2: 500 A3: 10 A4: text This formula: =LOOKUP(1E100,A2:A100) returns 10....not 500 The way that LOOKUP works is: .. It assumes the numbers in the list are in ascending order. .. It searches through the list for the lookup value (ignoring text). .. If the lookup value is larger than any item in the list, it returns the last item in the list. Note: This is a version that returns the last text value (ignoring numbers): =LOOKUP(REPT("z",255),A:A) Does that help? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Fred Smith" wrote in message ... The formula tells Excel to find the number 1e100 in the range A2:A100. 1e100 is a very large number (10 to the 100th power). As there is no number that large in the range, Excel returns the largest number it finds. Regards, Fred. "BROCK8292" wrote in message ... thats incredible, thank you very much im sure youre busy but if you get a chance i would love to know why that works br "T. Valko" wrote: Try this: =LOOKUP(1E100,A2:A100) Format as DATE -- Biff Microsoft Excel MVP "BROCK8292" wrote in message ... Im trying to select the last date in each of about 1000 seperate 50 row lists in a worksheet using a single cell at the top. Is there a formula that can do this? thank you, barry |
Is there a formula to select the last non-blank cell in a list
is there a way to return the largest number instead of the last?
that would actually work better for me "Dave Peterson" wrote: It returns the last number in that range--not the largest number in the range. Fred Smith wrote: The formula tells Excel to find the number 1e100 in the range A2:A100. 1e100 is a very large number (10 to the 100th power). As there is no number that large in the range, Excel returns the largest number it finds. Regards, Fred. "BROCK8292" wrote in message ... thats incredible, thank you very much im sure youre busy but if you get a chance i would love to know why that works br "T. Valko" wrote: Try this: =LOOKUP(1E100,A2:A100) Format as DATE -- Biff Microsoft Excel MVP "BROCK8292" wrote in message ... Im trying to select the last date in each of about 1000 seperate 50 row lists in a worksheet using a single cell at the top. Is there a formula that can do this? thank you, barry -- Dave Peterson |
Is there a formula to select the last non-blank cell in a list
thank you Ron,
is there a way to look up the largest number in that list, that would actually be more effective for what i am trying to do thanks for your time and effort, barry also, im trying to a range in one column based on a if the date in another is any of the last 7 days, i want to repeat this about 1000 times, is there an easier way to do this then with a nested sumif or a dsum formula? "Ron Coderre" wrote: There's an error in your explanation: With A1: 2 A2: 500 A3: 10 A4: text This formula: =LOOKUP(1E100,A2:A100) returns 10....not 500 The way that LOOKUP works is: .. It assumes the numbers in the list are in ascending order. .. It searches through the list for the lookup value (ignoring text). .. If the lookup value is larger than any item in the list, it returns the last item in the list. Note: This is a version that returns the last text value (ignoring numbers): =LOOKUP(REPT("z",255),A:A) Does that help? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Fred Smith" wrote in message ... The formula tells Excel to find the number 1e100 in the range A2:A100. 1e100 is a very large number (10 to the 100th power). As there is no number that large in the range, Excel returns the largest number it finds. Regards, Fred. "BROCK8292" wrote in message ... thats incredible, thank you very much im sure youre busy but if you get a chance i would love to know why that works br "T. Valko" wrote: Try this: =LOOKUP(1E100,A2:A100) Format as DATE -- Biff Microsoft Excel MVP "BROCK8292" wrote in message ... Im trying to select the last date in each of about 1000 seperate 50 row lists in a worksheet using a single cell at the top. Is there a formula that can do this? thank you, barry |
Is there a formula to select the last non-blank cell in a list
Just use MAX, like this:
=MAX(A2:A50) Hope this helps. Pete On Mar 24, 11:16*pm, BROCK8292 wrote: is there a way to return the largest number instead of the last? that would actually work better for me "Dave Peterson" wrote: It returns the last number in that range--not the largest number in the range. Fred Smith wrote: The formula tells Excel to find the number 1e100 in the range A2:A100. 1e100 is a very large number (10 to the 100th power). As there is no number that large in the range, Excel returns the largest number it finds. Regards, Fred. "BROCK8292" wrote in message ... thats incredible, thank you very much im sure youre busy but if you get a chance i would love to know why that works br "T. Valko" wrote: Try this: =LOOKUP(1E100,A2:A100) Format as DATE -- Biff Microsoft Excel MVP "BROCK8292" wrote in message ... Im trying to select the last date in each of about 1000 seperate 50 row lists in a worksheet using a single cell at the top. Is there a formula that can do this? thank you, barry -- Dave Peterson- Hide quoted text - - Show quoted text - |
Is there a formula to select the last non-blank cell in a list
perfect
thank you "Pete_UK" wrote: Just use MAX, like this: =MAX(A2:A50) Hope this helps. Pete On Mar 24, 11:16 pm, BROCK8292 wrote: is there a way to return the largest number instead of the last? that would actually work better for me "Dave Peterson" wrote: It returns the last number in that range--not the largest number in the range. Fred Smith wrote: The formula tells Excel to find the number 1e100 in the range A2:A100. 1e100 is a very large number (10 to the 100th power). As there is no number that large in the range, Excel returns the largest number it finds. Regards, Fred. "BROCK8292" wrote in message ... thats incredible, thank you very much im sure youre busy but if you get a chance i would love to know why that works br "T. Valko" wrote: Try this: =LOOKUP(1E100,A2:A100) Format as DATE -- Biff Microsoft Excel MVP "BROCK8292" wrote in message ... Im trying to select the last date in each of about 1000 seperate 50 row lists in a worksheet using a single cell at the top. Is there a formula that can do this? thank you, barry -- Dave Peterson- Hide quoted text - - Show quoted text - |
Is there a formula to select the last non-blank cell in a list
You're welcome.
Pete On Mar 24, 11:52*pm, BROCK8292 wrote: perfect thank you "Pete_UK" wrote: Just use MAX, like this: =MAX(A2:A50) Hope this helps. Pete On Mar 24, 11:16 pm, BROCK8292 wrote: is there a way to return the largest number instead of the last? that would actually work better for me "Dave Peterson" wrote: It returns the last number in that range--not the largest number in the range. Fred Smith wrote: The formula tells Excel to find the number 1e100 in the range A2:A100. 1e100 is a very large number (10 to the 100th power). As there is no number that large in the range, Excel returns the largest number it finds. Regards, Fred. "BROCK8292" wrote in message ... thats incredible, thank you very much im sure youre busy but if you get a chance i would love to know why that works br "T. Valko" wrote: Try this: =LOOKUP(1E100,A2:A100) Format as DATE -- Biff Microsoft Excel MVP "BROCK8292" wrote in message ... Im trying to select the last date in each of about 1000 seperate 50 row lists in a worksheet using a single cell at the top. Is there a formula that can do this? thank you, barry -- Dave Peterson- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| All times are GMT +1. The time now is 06:24 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com