|
Posted to microsoft.public.excel.worksheet.functions
|
|
|
Extract last and next-to-last entries in a range
Thank you very much.
"Biff" wrote:
Hi!
This will return the last and next to last cells that contain any value*:
Entered as an array using the key combination of CTRL,SHIFT,ENTER:
For the last value:
=INDEX(A$1:A$20,LARGE((A$1:A$20<"")*(ROW(A$1:A$20 )),ROWS($1:1)))
Just copy down to get the next to the last value.
* - if you have a formula that returns a formula blank ("") this formula
will not pick that up.
See this for an extensive overview:
http://xldynamic.com/source/xld.LastValue.html
Biff
"Quan" wrote in message
...
What if your data are a mixture of text and numbers or equations?
"Biff" wrote:
Ooops!
Typo:
=INDEX(A:A,MATCH(LOOKUP(REPT("z",255),A:A),A:A,0)-2)
Should be:
=INDEX(A:A,MATCH(LOOKUP(REPT("z",255),A:A),A:A,0)-1)
Biff
"Biff" wrote in message
...
Hi!
Assuming that the entries are text values:
For the next to the last entry:
=INDEX(A:A,MATCH(LOOKUP(REPT("z",255),A:A),A:A,0)-2)
For the last entry:
=LOOKUP(REPT("Z",255),A:A)
Biff
"Teri" wrote in message
...
I must set up a spreadsheet and extract the last and next-to-last
entries
in
certain columns. Those values will be linked to another sheet in the
workbook entitled "Summary". The columns are set up as shown below.
I
have
a second set of columns which are from 14:00 Friday July 29 through
8:00
Saturday July 30. I know this is a lot of info, but I'm such a novice
at
this. Any help would be greatly appreciated!
City Mile Marker Odometer
19:00 Thursday July 28
20:00 Thursday July 28
21:00 Thursday July 28
22:00 Thursday July 28
23:00 Thursday July 28
24:00 Thursday July 28
01:00 Friday July 29
02:00 Friday July 29
03:00 Friday July 29
04:00 Friday July 29
05:00 Friday July 29
06:00 Friday July 29
07:00 Friday July 29
08:00 Friday July 29
09:00 Friday July 29
10:00 Friday July 29
11:00 Friday July 29
12:00 Friday July 29
13:00 Friday July 29
|