Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Last NonBlank cell in a range
Does anybody know how to construct a worksheet function that displays the
location, or better still, the value, of the last non-blank cell in a large range like the second row in a spreadsheet for instance? O yes, and if you do know, could you explain how it works? Much appreciated in advance. Koffiepit |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Last NonBlank cell in a range
=LOOKUP(255,A2:Z2) - for numeric value
=LOOKUP(REPT("z",255),A2:Z2) - for text value best wishes Sreedhar "Koffiepit" wrote: Does anybody know how to construct a worksheet function that displays the location, or better still, the value, of the last non-blank cell in a large range like the second row in a spreadsheet for instance? O yes, and if you do know, could you explain how it works? Much appreciated in advance. Koffiepit |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Last NonBlank cell in a range
On Sat, 19 Apr 2008 21:26:00 -0700, yshridhar
wrote: =LOOKUP(255,A2:Z2) - for numeric value =LOOKUP(REPT("z",255),A2:Z2) - for text value best wishes Sreedhar "Koffiepit" wrote: Does anybody know how to construct a worksheet function that displays the location, or better still, the value, of the last non-blank cell in a large range like the second row in a spreadsheet for instance? O yes, and if you do know, could you explain how it works? Much appreciated in advance. Koffiepit Assuming "the last non-blank cell" is defined as the rightmost non-blank cell in the last row of the range that has any non-blank cell you can try this to get the value og the last non-blank cell: =OFFSET(A1,INT(MAX(NOT(ISBLANK(myrange))*(COLUMNS( 1:1)*ROW(myrange)+COLUMN(myrange)))/COLUMNS(1:1))-1, MOD(MAX(NOT(ISBLANK(myrange))*(COLUMNS(1:1)*ROW(my range)+COLUMN(myrange)))/COLUMNS(1:1),1)*COLUMNS(1:1)-1) myrange is the range in question. This should be entered, everything on one line, as an array formula, i.e. by pressing CTRL+SHIFT+ENTER rather than just ENTER This is how it works: The ISBLANK(myrange) part gives an array with TRUE for all blank cells and FALSE for all non-blank cell The COLUMNS(1:1)*ROW(myrange)+COLUMN(myrange) part gives an array with a "cell number" for each cell in the range. The number COLUMNS(1:1) part is there to make the number unique for all cells. When these two arrays are multiplied you get an array with zeroes for the blank cells and the "cell number" for the non-blank cells. MAX then finds the maximum "cell number" for a non-blank cell. The INT and MOD parts are there to transform the cell number to row and column for the cell. INT(MAX(NOT(ISBLANK(myrange))*(COLUMNS(1:1)*ROW(my range)+COLUMN(myrange)))/COLUMNS(1:1)) gives the row number (r) of the cell MOD(MAX(NOT(ISBLANK(myrange))*(COLUMNS(1:1)*ROW(my range)+COLUMN(myrange)))/COLUMNS(1:1),1)*COLUMNS(1:1) gives the column number (c) of the cell and OFFSET(A1,r-1,c-1) finally gives the value of the cell with row number r and column number c Hope this helps / Lars-Åke |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Last NonBlank cell in a range
For the last numerical value in a range, try...
=LOOKUP(9.99999999999999E+307,Range) Hope this helps! In article , "Koffiepit" wrote: Does anybody know how to construct a worksheet function that displays the location, or better still, the value, of the last non-blank cell in a large range like the second row in a spreadsheet for instance? O yes, and if you do know, could you explain how it works? Much appreciated in advance. Koffiepit |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Last NonBlank cell in a range
This will display the value in the last non-blank cell in Row2, *either*
Text or Number: =LOOKUP(2,1/(2:2<""),2:2) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Koffiepit" wrote in message ... Does anybody know how to construct a worksheet function that displays the location, or better still, the value, of the last non-blank cell in a large range like the second row in a spreadsheet for instance? O yes, and if you do know, could you explain how it works? Much appreciated in advance. Koffiepit |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Last NonBlank cell in a range
If you would like to see how it works, change the formula to a smaller range
so that the formula evaluation will display - without a "Too Large" error. Say 10 cells: =LOOKUP(2,1/(A2:J2<""),A2:J2) Enter this formula in A1, and put a value in any 2 cells within the range. Now, in the formula bar, select *only*: (A2:J2<"") And hit <F9 You see an array of True and False, where the populated cells return True. Hit <Esc to revert back to the formula without destroying it. Trues evaluate to 1's and Falses to 0's. So, now select in the formula bar *only*: 1/(A2:J2<"") And hit <F9 You now see an array of 1's and #DIV/0! errors. This comes from dividing 1 by 1 and 1 by 0. Hit <Esc In this form of the Lookup function, the one dimension lookup vector is between the first and second commas. This means the lookup vector is *NOT* A2:J2, But *IS* the array of 1's and #DIV/0! errors. The lookup value in this formula is 2, but there is *no* 2 in the lookup vector. NOW, follow this anomaly: The Lookup() function pre-supposes that the lookup vector is sorted, ascending. If it can't find the lookup value, it's programmed to find the largest value in the lookup vector that is *less* than or equal to the lookup value. It by-passes the errors, and since it believes that the lookup vector is sorted ascending, it returns the *last* 1, which, if sorted, *should* be the largest value that's less then the lookup value. So here, the lookup value of 2 cannot exist and is *never* found. This anomaly can also work if you're just looking for the last numerical value in a range, or just the last text value in a range. For numbers, make the lookup value larger then any number that may possibly exist in the lookup vector: =LOOKUP(99^99,2:2) And the same concept for text: =LOOKUP(REPT("z",255),2:2) -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Ragdyer" wrote in message ... This will display the value in the last non-blank cell in Row2, *either* Text or Number: =LOOKUP(2,1/(2:2<""),2:2) -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Koffiepit" wrote in message ... Does anybody know how to construct a worksheet function that displays the location, or better still, the value, of the last non-blank cell in a large range like the second row in a spreadsheet for instance? O yes, and if you do know, could you explain how it works? Much appreciated in advance. Koffiepit |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Last NonBlank cell in a range
Hi Raqdyer,
Just jumping in, thanks for the function, and the explanation. Understanding is always better than just using. Regards - Dave. "Ragdyer" wrote: If you would like to see how it works, change the formula to a smaller range so that the formula evaluation will display - without a "Too Large" error. Say 10 cells: =LOOKUP(2,1/(A2:J2<""),A2:J2) Enter this formula in A1, and put a value in any 2 cells within the range. Now, in the formula bar, select *only*: (A2:J2<"") And hit <F9 You see an array of True and False, where the populated cells return True. Hit <Esc to revert back to the formula without destroying it. Trues evaluate to 1's and Falses to 0's. So, now select in the formula bar *only*: 1/(A2:J2<"") And hit <F9 You now see an array of 1's and #DIV/0! errors. This comes from dividing 1 by 1 and 1 by 0. Hit <Esc In this form of the Lookup function, the one dimension lookup vector is between the first and second commas. This means the lookup vector is *NOT* A2:J2, But *IS* the array of 1's and #DIV/0! errors. The lookup value in this formula is 2, but there is *no* 2 in the lookup vector. NOW, follow this anomaly: The Lookup() function pre-supposes that the lookup vector is sorted, ascending. If it can't find the lookup value, it's programmed to find the largest value in the lookup vector that is *less* than or equal to the lookup value. It by-passes the errors, and since it believes that the lookup vector is sorted ascending, it returns the *last* 1, which, if sorted, *should* be the largest value that's less then the lookup value. So here, the lookup value of 2 cannot exist and is *never* found. This anomaly can also work if you're just looking for the last numerical value in a range, or just the last text value in a range. For numbers, make the lookup value larger then any number that may possibly exist in the lookup vector: =LOOKUP(99^99,2:2) And the same concept for text: =LOOKUP(REPT("z",255),2:2) -- Regards, |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Last NonBlank cell in a range
You're quite welcome!
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Dave" wrote in message ... Hi Raqdyer, Just jumping in, thanks for the function, and the explanation. Understanding is always better than just using. Regards - Dave. "Ragdyer" wrote: If you would like to see how it works, change the formula to a smaller range so that the formula evaluation will display - without a "Too Large" error. Say 10 cells: =LOOKUP(2,1/(A2:J2<""),A2:J2) Enter this formula in A1, and put a value in any 2 cells within the range. Now, in the formula bar, select *only*: (A2:J2<"") And hit <F9 You see an array of True and False, where the populated cells return True. Hit <Esc to revert back to the formula without destroying it. Trues evaluate to 1's and Falses to 0's. So, now select in the formula bar *only*: 1/(A2:J2<"") And hit <F9 You now see an array of 1's and #DIV/0! errors. This comes from dividing 1 by 1 and 1 by 0. Hit <Esc In this form of the Lookup function, the one dimension lookup vector is between the first and second commas. This means the lookup vector is *NOT* A2:J2, But *IS* the array of 1's and #DIV/0! errors. The lookup value in this formula is 2, but there is *no* 2 in the lookup vector. NOW, follow this anomaly: The Lookup() function pre-supposes that the lookup vector is sorted, ascending. If it can't find the lookup value, it's programmed to find the largest value in the lookup vector that is *less* than or equal to the lookup value. It by-passes the errors, and since it believes that the lookup vector is sorted ascending, it returns the *last* 1, which, if sorted, *should* be the largest value that's less then the lookup value. So here, the lookup value of 2 cannot exist and is *never* found. This anomaly can also work if you're just looking for the last numerical value in a range, or just the last text value in a range. For numbers, make the lookup value larger then any number that may possibly exist in the lookup vector: =LOOKUP(99^99,2:2) And the same concept for text: =LOOKUP(REPT("z",255),2:2) -- Regards, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Nonblank Cell | Excel Worksheet Functions | |||
how to return the value of the last nonblank cell in a row? | Excel Discussion (Misc queries) | |||
Require a cell to be nonblank if another cell is nonblank | Excel Discussion (Misc queries) | |||
Display first, second, etc Nonblank Cells in a Range | Excel Worksheet Functions | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions |