| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Get final non-blank cell in range
I've got a range where I'm including a bunch of values. I need a formula
which will return the last value non-blank in the range. Not the highest or lowest, simply the last. Thanks. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Get final non-blank cell in range
One way:
=LOOKUP(9.99999999999999E+307,A:A) Biff "Eric" wrote in message ... I've got a range where I'm including a bunch of values. I need a formula which will return the last value non-blank in the range. Not the highest or lowest, simply the last. Thanks. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Get final non-blank cell in range
Hi Eric,
Just to add to Biff's solution, if you knew for absolute sure that the largest number in the column of interest would never exceed 365, then you could use =LOOKUP(366,A:A) HTH Regards, Howard "Eric" wrote in message ... I've got a range where I'm including a bunch of values. I need a formula which will return the last value non-blank in the range. Not the highest or lowest, simply the last. Thanks. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Get final non-blank cell in range
OR ... something like:
=LOOKUP(99^99,A:A) -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "L. Howard Kittle" wrote in message . .. Hi Eric, Just to add to Biff's solution, if you knew for absolute sure that the largest number in the column of interest would never exceed 365, then you could use =LOOKUP(366,A:A) HTH Regards, Howard "Eric" wrote in message ... I've got a range where I'm including a bunch of values. I need a formula which will return the last value non-blank in the range. Not the highest or lowest, simply the last. Thanks. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Get final non-blank cell in range
1. The help text for LOOKUP says the values have to be in order. But in my
case, they won't always be. Will it still work even if not in order? 2. What if the values are a mix of numbers and alpha values? "Biff" wrote: One way: =LOOKUP(9.99999999999999E+307,A:A) Biff "Eric" wrote in message ... I've got a range where I'm including a bunch of values. I need a formula which will return the last value non-blank in the range. Not the highest or lowest, simply the last. Thanks. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Get final non-blank cell in range
Based on this statement:
Not the highest or lowest, simply the last. I assumed you were talking about NUMBERS in which case the formula would work whether the numbers were sorted or not. 2. What if the values are a mix of numbers and alpha values? If you want the last value, text or number (excludes formula blanks: ""): =LOOKUP(2,1/(A1:A20<""),A1:A20) Biff "Eric" wrote in message ... 1. The help text for LOOKUP says the values have to be in order. But in my case, they won't always be. Will it still work even if not in order? 2. What if the values are a mix of numbers and alpha values? "Biff" wrote: One way: =LOOKUP(9.99999999999999E+307,A:A) Biff "Eric" wrote in message ... I've got a range where I'm including a bunch of values. I need a formula which will return the last value non-blank in the range. Not the highest or lowest, simply the last. Thanks. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Get final non-blank cell in range
Or even:
=LOOKUP(MAX(A:A)+1,A:A) Biff "RagDyer" wrote in message ... OR ... something like: =LOOKUP(99^99,A:A) -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "L. Howard Kittle" wrote in message . .. Hi Eric, Just to add to Biff's solution, if you knew for absolute sure that the largest number in the column of interest would never exceed 365, then you could use =LOOKUP(366,A:A) HTH Regards, Howard "Eric" wrote in message ... I've got a range where I'm including a bunch of values. I need a formula which will return the last value non-blank in the range. Not the highest or lowest, simply the last. Thanks. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Get final non-blank cell in range
Harlan might say:
"Unneeded function call." -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "Biff" wrote in message ... Or even: =LOOKUP(MAX(A:A)+1,A:A) Biff "RagDyer" wrote in message ... OR ... something like: =LOOKUP(99^99,A:A) -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "L. Howard Kittle" wrote in message . .. Hi Eric, Just to add to Biff's solution, if you knew for absolute sure that the largest number in the column of interest would never exceed 365, then you could use =LOOKUP(366,A:A) HTH Regards, Howard "Eric" wrote in message ... I've got a range where I'm including a bunch of values. I need a formula which will return the last value non-blank in the range. Not the highest or lowest, simply the last. Thanks. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Get final non-blank cell in range
Harlan might say:
"Unneeded function call." Or Aladin! Yeah, but if the range is "small" it's a lot less confusing than 9.99999999999999E+307 or 99^99. Biff "RagDyeR" wrote in message ... Harlan might say: "Unneeded function call." -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "Biff" wrote in message ... Or even: =LOOKUP(MAX(A:A)+1,A:A) Biff "RagDyer" wrote in message ... OR ... something like: =LOOKUP(99^99,A:A) -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "L. Howard Kittle" wrote in message . .. Hi Eric, Just to add to Biff's solution, if you knew for absolute sure that the largest number in the column of interest would never exceed 365, then you could use =LOOKUP(366,A:A) HTH Regards, Howard "Eric" wrote in message ... I've got a range where I'm including a bunch of values. I need a formula which will return the last value non-blank in the range. Not the highest or lowest, simply the last. Thanks. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Get final non-blank cell in range
I'm prejudiced!
I think: =LOOKUP(99^99,A:A) Looks neat!<bg -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "Biff" wrote in message ... Harlan might say: "Unneeded function call." Or Aladin! Yeah, but if the range is "small" it's a lot less confusing than 9.99999999999999E+307 or 99^99. Biff "RagDyeR" wrote in message ... Harlan might say: "Unneeded function call." -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "Biff" wrote in message ... Or even: =LOOKUP(MAX(A:A)+1,A:A) Biff "RagDyer" wrote in message ... OR ... something like: =LOOKUP(99^99,A:A) -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "L. Howard Kittle" wrote in message . .. Hi Eric, Just to add to Biff's solution, if you knew for absolute sure that the largest number in the column of interest would never exceed 365, then you could use =LOOKUP(366,A:A) HTH Regards, Howard "Eric" wrote in message ... I've got a range where I'm including a bunch of values. I need a formula which will return the last value non-blank in the range. Not the highest or lowest, simply the last. Thanks. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Record changing cell data into a column or range | Excel Worksheet Functions | |||
| Cell address in a range | Excel Discussion (Misc queries) | |||
| Match function...random search? | Excel Worksheet Functions | |||
| blank cell turns to 0 | New Users to Excel | |||
| VLookup resulting in a blank cell... | Excel Worksheet Functions | |||
Linear Mode
