Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How can I locate the second to last value in a range?
I am trying to locate the last value in a column and then the second to last,
then third to last and so on to be placed in a seperate section of my spreadsheet. I am using the following formula to locate the last cell which is: Lookup(2,1/(A3:A145<"")),A3:A145) Where do I go from here? Thanks for the help |
#2
|
|||
|
|||
=offset(a1, [your formula from below]-1,0,1,1) will give you the last value
in col A. Change the -1 to -2, -3 , etc for the other cells "A shink" wrote: I am trying to locate the last value in a column and then the second to last, then third to last and so on to be placed in a seperate section of my spreadsheet. I am using the following formula to locate the last cell which is: Lookup(2,1/(A3:A145<"")),A3:A145) Where do I go from here? Thanks for the help |
#3
|
|||
|
|||
Duke:
That addition to the formula actually gives me the first filled row of the column not the last. Anyother ideas? thanks "Duke Carey" wrote: =offset(a1, [your formula from below]-1,0,1,1) will give you the last value in col A. Change the -1 to -2, -3 , etc for the other cells "A shink" wrote: I am trying to locate the last value in a column and then the second to last, then third to last and so on to be placed in a seperate section of my spreadsheet. I am using the following formula to locate the last cell which is: Lookup(2,1/(A3:A145<"")),A3:A145) Where do I go from here? Thanks for the help |
#4
|
|||
|
|||
Try this formula for the LAST value. As before, adjust the -1 to get the
previous cell, etc =OFFSET(A1,MATCH(9.99999999999999E+307,A:A)-1,0,1,1) "A shink" wrote: Duke: That addition to the formula actually gives me the first filled row of the column not the last. Anyother ideas? thanks "Duke Carey" wrote: =offset(a1, [your formula from below]-1,0,1,1) will give you the last value in col A. Change the -1 to -2, -3 , etc for the other cells "A shink" wrote: I am trying to locate the last value in a column and then the second to last, then third to last and so on to be placed in a seperate section of my spreadsheet. I am using the following formula to locate the last cell which is: Lookup(2,1/(A3:A145<"")),A3:A145) Where do I go from here? Thanks for the help |
#5
|
|||
|
|||
Thank you for all the help!!!!
"Duke Carey" wrote: Try this formula for the LAST value. As before, adjust the -1 to get the previous cell, etc =OFFSET(A1,MATCH(9.99999999999999E+307,A:A)-1,0,1,1) "A shink" wrote: Duke: That addition to the formula actually gives me the first filled row of the column not the last. Anyother ideas? thanks "Duke Carey" wrote: =offset(a1, [your formula from below]-1,0,1,1) will give you the last value in col A. Change the -1 to -2, -3 , etc for the other cells "A shink" wrote: I am trying to locate the last value in a column and then the second to last, then third to last and so on to be placed in a seperate section of my spreadsheet. I am using the following formula to locate the last cell which is: Lookup(2,1/(A3:A145<"")),A3:A145) Where do I go from here? Thanks for the help |
#6
|
|||
|
|||
A shink wrote:
I am trying to locate the last value in a column and then the second to last, then third to last and so on to be placed in a seperate section of my spreadsheet. I am using the following formula to locate the last cell which is: Lookup(2,1/(A3:A145<"")),A3:A145) Where do I go from here? Thanks for the help Last numeric value: =LOOKUP(9.99999999999999E+307,A3:A145) Second to the last numeric value: =LOOKUP(9.99999999999999E+307,A3:INDEX(A3:A145,MAT CH(9.99999999999999E+307,A3:A145)-1)) If you meant to "locate" any value (last entry of any type), a different type of formula is required. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 hangs when trying to locate a workbook to open | Excel Discussion (Misc queries) | |||
How do you locate External References in an Excel Workbook | Excel Discussion (Misc queries) | |||
Locate and delete specific cells | Excel Discussion (Misc queries) | |||
how do I locate a cell that provides data to a formula ? | Excel Worksheet Functions | |||
how do I locate a cell that provides data to a formula ? | Excel Worksheet Functions |