Home |
Search |
Today's Posts |
#1
|
|||
|
|||
find last cell in column - without VBA
Hi,
Apart from using VBA, is there any way to write a formula that will find the last used cell in a column of 15 cells? In the first set below, it would be ..388, in the second set it would be .133. Any help would be appreciated. 0.006 0.000 0.036 0.125 0.133 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.313 0.388 0.006 0.000 0.036 0.125 0.133 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 |
#2
|
|||
|
|||
Harold,
One way, for numbers in A1:A15: =INDEX(A1:A15,SUMPRODUCT(MAX((A1:A150)*ROW(A1:A15 )))) HTH, Bernie MS Excel MVP "Harold Good" wrote in message ... Hi, Apart from using VBA, is there any way to write a formula that will find the last used cell in a column of 15 cells? In the first set below, it would be .388, in the second set it would be .133. Any help would be appreciated. 0.006 0.000 0.036 0.125 0.133 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.313 0.388 0.006 0.000 0.036 0.125 0.133 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 |
#3
|
|||
|
|||
Thanks again, I really appreciate this one, I never thought of combining
these functions to come up with this. Harold "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Harold, One way, for numbers in A1:A15: =INDEX(A1:A15,SUMPRODUCT(MAX((A1:A150)*ROW(A1:A15 )))) HTH, Bernie MS Excel MVP "Harold Good" wrote in message ... Hi, Apart from using VBA, is there any way to write a formula that will find the last used cell in a column of 15 cells? In the first set below, it would be .388, in the second set it would be .133. Any help would be appreciated. 0.006 0.000 0.036 0.125 0.133 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.313 0.388 0.006 0.000 0.036 0.125 0.133 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 |
#4
|
|||
|
|||
|
#5
|
|||
|
|||
It worked great, thanks,
Harold "Sandy Mann" wrote in message ... One way: =LOOKUP(2,1/(A1:A150),A1:A15) -- HTH Sandy Replace@mailinator with @tiscali.co.uk "Harold Good" wrote in message ... Hi, Apart from using VBA, is there any way to write a formula that will find the last used cell in a column of 15 cells? In the first set below, it would be .388, in the second set it would be .133. Any help would be appreciated. 0.006 0.000 0.036 0.125 0.133 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.313 0.388 0.006 0.000 0.036 0.125 0.133 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Table Dilemma | Excel Worksheet Functions | |||
Help with macro looping and color query function | Excel Discussion (Misc queries) | |||
search a row to find the column | Excel Worksheet Functions | |||
How do I reference every "n" cell in a column in Excel? | Excel Worksheet Functions | |||
In MS Excel, how do I fill in a column with the same cell from se. | Excel Worksheet Functions |