columns:Extract last and next-to-last entries in a range

Aladin,

With
> =LOOKUP(REPT("z",255),A2:INDEX(A2:A65536,MATCH(REPT("z",255),A2:A65536)))

I get the last value again unless I add a *-1* after the MATCH function:

=LOOKUP(REPT("z",255),A2:INDEX(A2:A65536,MATCH(REPT("z",255),A2:A65536)-1))

My last test data was in row 27 and the MATCH function returned 26. When
the INDEX indexed 26 down from A2 it of course found the last entry in A27.


Actually now that I have selected *Show downloaded messages* I see that
Biff's
reply included the -1. Is it required in you formula or am I missing
something?

--
Regards


Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"Aladin Akyurek" <[email protected]> wrote in message
news:[email protected]...
> Assuming that the data start at row 2 in column A and the values of
> interest are text...
>
> Last text value:
>
> =LOOKUP(REPT("z",255),A2:A65536)
>
> Next to last value:
>
> =LOOKUP(REPT("z",255),A2:INDEX(A2:A65536,MATCH(REPT("z",255),A2:A65536)))
>
> This formulas would return a formula-blank (i.e., "") if such is the last
> (or the next-to-last value).
>
> Teri wrote:
>> 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
>>
>
> --
>
> [1] The SumProduct function should implicitly coerce the truth values to
> their Excel numeric equivalents.
> [2] The lookup functions should have an optional argument for the return
> value, defaulting to #N/A in its absence.

Hi!

> The issue in this thread wasn't one of efficiency, but correctness.

Well, let's see about that! <g>

Your formula uses logic that accounts for empty cells in the range, however,
the OP's sample data did not include any empty cells in the range. There was
also no mention of the possibility of empty cells in the range. So I based
my formulas on what was posted.

While it is probably not a good practice to assume things that are not
posted, it's good to be prepared for the possibilities.

But taking that into account and by the same token, then you also have done
what you want to correct me for.

Your formulas assume the values in the range are text. They may have been
formatted date/times. There may be both text and numeric values in the
range. If you're going to account for the possibility of empty cells in the
range then shouldn't you also account for mixed data types?

Gotcha! <g>

Thanks for the info regarding efficiency. I appreciate the contributions you
make here and have learned a great deal from you.

Biff

"Aladin Akyurek" <[email protected]> wrote in message
news:[email protected]...
> Biff wrote:
>> Hi!
>>
>> Frequently, you post options to others replies and claim that they are
>> more efficient.
>
> Biff,
>
> The issue in this thread wasn't one of efficiency, but correctness.
>
> Given:
>
> New York
> New Jersey
> Empty
> Empty
> Ohio
>
> in A2:A6
>
> =INDEX(A:A,MATCH(LOOKUP(REPT("z",255),A:A),A:A,0)-1)
>
> cannot capture the next-to-last text value. While, with missing -1
> added...
>
> =LOOKUP(REPT("z",255),A2:INDEX(A2:A65536,MATCH(REPT("z",255),A2:A65536)-1))
>
> will do so.
>
> Regarding efficiency issues, a recent trend is
>
> http://tinyurl.com/axrvo
>
> where I "claimed" a certain formula to be more efficent than another. That
> is:
>
> =LOOKUP(9.99999999999999E+307,L:L)
>
> is efficient compared to
>
> =LOOKUP(2,1/ISNUMBER(L1:L1000),L1:L1000)
>
> for the task of fetching the last numeric value from a range, consisting
> of either manual entries or calculated values. There is no need to back up
> this particular claim with any benchmarking or timing software. See the
> discussion in that thread for why this should be so.
>
> You also don't need to construct a temporal profile to claim that:
>
> =SUMIF($C$4:$C$15,"S",$H$4:$H$*15)
>
> is faster than:
>
> =SUMPRODUCT(--($C$4:$C$15="S")*,$H$4:$H$15)
>
> or
>
> {=SUM(IF($C$4:$C$15="S",,$H$4:$H$15)}
>
> for SumIf operates on range objects, not on array objects as the latter
> two must, therefore faster.
>
> To add just another example...
>
> =LOOKUP(E2&F2,{"N","";"NN","";*"NY","D";"Y","";"YN","D";"YY",*"P"})
>
> has a better temporal score than
>
> =IF(OR(E2="",F2=""),"",IF(COUN*TIF(E2:F2,"Y")=2,"P",IF(OR(AND*(E2="Y",F2="N"),
> AND(E2="N",F2="Y")),"D","")))
>
> Do we need to profile them? I don't think so.
>
> Some rules of thumb, derived from the knowledge of the behavior of the
> functions (possible underlying algorithms the functions invoke), are:
>
> 1. Calculating on range objects is faster than calculating on array
> objects.
>
> 2. Lookup functions that resort to binary search are faster than lookup
> functions which are set up to invoke linear search.
>
> 3. A formula with lesser number of function calls, all things being equal,
> is better than one that invokes a multitude of functions.
>
> 4. The formulas without volatile functions are generally faster than the
> formulas which invoke INDIRECT, OFFSET, etc. Also, OFFSET() is better than
> INDIRECT(), couppled with ADDRESS().
>
> 5. Calculating on the relevant subranges is faster than on the whole
> range.
>
> The foregoing list is by no means exhaustive.
>
>>
>> I'm just wondering how you test these for efficiency? Particularly, the
>> speed of calculation. Do you use some type of benchmarking software or do
>> you have VBA routines that do this?
>>
>> I'm always interested in improving my approaches to problems so any
>> insight you can offer would be greatly appreciated.
>>
>> Biff
>
> In a not neglible number of cases one needs to profile formulas. Charles
> Williams's FastExcel meets this need nicely.
>
>>
>> "Aladin Akyurek" <[email protected]> wrote in message
>> news:[email protected]...
>>
>>>Assuming that the data start at row 2 in column A and the values of
>>>interest are text...
>>>
>>>Last text value:
>>>
>>>=LOOKUP(REPT("z",255),A2:A65536)
>>>
>>>Next to last value:
>>>
>>>=LOOKUP(REPT("z",255),A2:INDEX(A2:A65536,MATCH(REPT("z",255),A2:A65536)))
>>>
>>>This formulas would return a formula-blank (i.e., "") if such is the last
>>>(or the next-to-last value).
>
>

Biff wrote:
> Hi!
>
> Frequently, you post options to others replies and claim that they are more
> efficient.

Biff,

The issue in this thread wasn't one of efficiency, but correctness.

Given:

New York
New Jersey
Empty
Empty
Ohio

in A2:A6

=INDEX(A:A,MATCH(LOOKUP(REPT("z",255),A:A),A:A,0)-1)

cannot capture the next-to-last text value. While, with missing -1 added...

=LOOKUP(REPT("z",255),A2:INDEX(A2:A65536,MATCH(REPT("z",255),A2:A65536)-1))

will do so.

Regarding efficiency issues, a recent trend is

http://tinyurl.com/axrvo

where I "claimed" a certain formula to be more efficent than another.
That is:

=LOOKUP(9.99999999999999E+307,L:L)

is efficient compared to

=LOOKUP(2,1/ISNUMBER(L1:L1000),L1:L1000)

for the task of fetching the last numeric value from a range, consisting
of either manual entries or calculated values. There is no need to back
up this particular claim with any benchmarking or timing software. See
the discussion in that thread for why this should be so.

You also don't need to construct a temporal profile to claim that:

=SUMIF($C$4:$C$15,"S",$H$4:$H$*15)

is faster than:

=SUMPRODUCT(--($C$4:$C$15="S")*,$H$4:$H$15)

or

{=SUM(IF($C$4:$C$15="S",,$H$4:$H$15)}

for SumIf operates on range objects, not on array objects as the latter
two must, therefore faster.

To add just another example...

=LOOKUP(E2&F2,{"N","";"NN","";*"NY","D";"Y","";"YN","D";"YY",*"P"})

has a better temporal score than

=IF(OR(E2="",F2=""),"",IF(COUN*TIF(E2:F2,"Y")=2,"P",IF(OR(AND*(E2="Y",F2="N"),

AND(E2="N",F2="Y")),"D","")))

Do we need to profile them? I don't think so.

Some rules of thumb, derived from the knowledge of the behavior of the
functions (possible underlying algorithms the functions invoke), are:

1. Calculating on range objects is faster than calculating on array objects.

2. Lookup functions that resort to binary search are faster than lookup
functions which are set up to invoke linear search.

3. A formula with lesser number of function calls, all things being
equal, is better than one that invokes a multitude of functions.

4. The formulas without volatile functions are generally faster than the
formulas which invoke INDIRECT, OFFSET, etc. Also, OFFSET() is better
than INDIRECT(), couppled with ADDRESS().

5. Calculating on the relevant subranges is faster than on the whole range.

The foregoing list is by no means exhaustive.

>
> I'm just wondering how you test these for efficiency? Particularly, the
> speed of calculation. Do you use some type of benchmarking software or do
> you have VBA routines that do this?
>
> I'm always interested in improving my approaches to problems so any insight
> you can offer would be greatly appreciated.
>
> Biff

In a not neglible number of cases one needs to profile formulas. Charles
Williams's FastExcel meets this need nicely.

>
> "Aladin Akyurek" <[email protected]> wrote in message
> news:[email protected]...
>
>>Assuming that the data start at row 2 in column A and the values of
>>interest are text...
>>
>>Last text value:
>>
>>=LOOKUP(REPT("z",255),A2:A65536)
>>
>>Next to last value:
>>
>>=LOOKUP(REPT("z",255),A2:INDEX(A2:A65536,MATCH(REPT("z",255),A2:A65536)))
>>
>>This formulas would return a formula-blank (i.e., "") if such is the last
>>(or the next-to-last value).

Sandy Mann wrote:
> Aladin,
>
> With
>
>>=LOOKUP(REPT("z",255),A2:INDEX(A2:A65536,MATCH(REPT("z",255),A2:A65536)))
>
>
> I get the last value again unless I add a *-1* after the MATCH function:
>
> =LOOKUP(REPT("z",255),A2:INDEX(A2:A65536,MATCH(REPT("z",255),A2:A65536)-1))
>
> My last test data was in row 27 and the MATCH function returned 26. When
> the INDEX indexed 26 down from A2 it of course found the last entry in A27.
>
>
> Actually now that I have selected *Show downloaded messages* I see that
> Biff's
> reply included the -1. Is it required in you formula or am I missing
> something?
>

Sandy,

That's right. Thanks for catching that.

Hi!

Frequently, you post options to others replies and claim that they are more
efficient.

I'm just wondering how you test these for efficiency? Particularly, the
speed of calculation. Do you use some type of benchmarking software or do
you have VBA routines that do this?

I'm always interested in improving my approaches to problems so any insight
you can offer would be greatly appreciated.

Biff

"Aladin Akyurek" <[email protected]> wrote in message
news:[email protected]...
> Assuming that the data start at row 2 in column A and the values of
> interest are text...
>
> Last text value:
>
> =LOOKUP(REPT("z",255),A2:A65536)
>
> Next to last value:
>
> =LOOKUP(REPT("z",255),A2:INDEX(A2:A65536,MATCH(REPT("z",255),A2:A65536)))
>
> This formulas would return a formula-blank (i.e., "") if such is the last
> (or the next-to-last value).
>
> Teri wrote:
>> 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
>>
>
> --
>
> [1] The SumProduct function should implicitly coerce the truth values to
> their Excel numeric equivalents.
> [2] The lookup functions should have an optional argument for the return
> value, defaulting to #N/A in its absence.

Assuming that the data start at row 2 in column A and the values of
interest are text...

Last text value:

=LOOKUP(REPT("z",255),A2:A65536)

Next to last value:

=LOOKUP(REPT("z",255),A2:INDEX(A2:A65536,MATCH(REPT("z",255),A2:A65536)))

This formulas would return a formula-blank (i.e., "") if such is the
last (or the next-to-last value).

Teri wrote:
> 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
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>>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
>>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
>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
>

Assuming that the data start at row 2 in column A and the values of
interest are text...

Last text value:

=LOOKUP(REPT("z",255),A2:A65536)

Next to last value:

=LOOKUP(REPT("z",255),A2:INDEX(A2:A65536,MATCH(REPT("z",255),A2:A65536)))

This formulas would return a formula-blank (i.e., "") if such is the
last (or the next-to-last value).

Teri wrote:
> 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
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

Aladin,

With
> =LOOKUP(REPT("z",255),A2:INDEX(A2:A65536,MATCH(REPT("z",255),A2:A65536)))

I get the last value again unless I add a *-1* after the MATCH function:

=LOOKUP(REPT("z",255),A2:INDEX(A2:A65536,MATCH(REPT("z",255),A2:A65536)-1))

My last test data was in row 27 and the MATCH function returned 26. When
the INDEX indexed 26 down from A2 it of course found the last entry in A27.


Actually now that I have selected *Show downloaded messages* I see that
Biff's
reply included the -1. Is it required in you formula or am I missing
something?

--
Regards


Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"Aladin Akyurek" <[email protected]> wrote in message
news:[email protected]...
> Assuming that the data start at row 2 in column A and the values of
> interest are text...
>
> Last text value:
>
> =LOOKUP(REPT("z",255),A2:A65536)
>
> Next to last value:
>
> =LOOKUP(REPT("z",255),A2:INDEX(A2:A65536,MATCH(REPT("z",255),A2:A65536)))
>
> This formulas would return a formula-blank (i.e., "") if such is the last
> (or the next-to-last value).
>
> Teri wrote:
>> 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
>>
>
> --
>
> [1] The SumProduct function should implicitly coerce the truth values to
> their Excel numeric equivalents.
> [2] The lookup functions should have an optional argument for the return
> value, defaulting to #N/A in its absence.

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

Hi!

Frequently, you post options to others replies and claim that they are more
efficient.

I'm just wondering how you test these for efficiency? Particularly, the
speed of calculation. Do you use some type of benchmarking software or do
you have VBA routines that do this?

I'm always interested in improving my approaches to problems so any insight
you can offer would be greatly appreciated.

Biff

"Aladin Akyurek" <[email protected]> wrote in message
news:[email protected]...
> Assuming that the data start at row 2 in column A and the values of
> interest are text...
>
> Last text value:
>
> =LOOKUP(REPT("z",255),A2:A65536)
>
> Next to last value:
>
> =LOOKUP(REPT("z",255),A2:INDEX(A2:A65536,MATCH(REPT("z",255),A2:A65536)))
>
> This formulas would return a formula-blank (i.e., "") if such is the last
> (or the next-to-last value).
>
> Teri wrote:
>> 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
>>
>
> --
>
> [1] The SumProduct function should implicitly coerce the truth values to
> their Excel numeric equivalents.
> [2] The lookup functions should have an optional argument for the return
> value, defaulting to #N/A in its absence.

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>>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
>>
>
>

Sandy Mann wrote:
> Aladin,
>
> With
>
>>=LOOKUP(REPT("z",255),A2:INDEX(A2:A65536,MATCH(REPT("z",255),A2:A65536)))
>
>
> I get the last value again unless I add a *-1* after the MATCH function:
>
> =LOOKUP(REPT("z",255),A2:INDEX(A2:A65536,MATCH(REPT("z",255),A2:A65536)-1))
>
> My last test data was in row 27 and the MATCH function returned 26. When
> the INDEX indexed 26 down from A2 it of course found the last entry in A27.
>
>
> Actually now that I have selected *Show downloaded messages* I see that
> Biff's
> reply included the -1. Is it required in you formula or am I missing
> something?
>

Sandy,

That's right. Thanks for catching that.

Biff wrote:
> Hi!
>
> Frequently, you post options to others replies and claim that they are more
> efficient.

Biff,

The issue in this thread wasn't one of efficiency, but correctness.

Given:

New York
New Jersey
Empty
Empty
Ohio

in A2:A6

=INDEX(A:A,MATCH(LOOKUP(REPT("z",255),A:A),A:A,0)-1)

cannot capture the next-to-last text value. While, with missing -1 added...

=LOOKUP(REPT("z",255),A2:INDEX(A2:A65536,MATCH(REPT("z",255),A2:A65536)-1))

will do so.

Regarding efficiency issues, a recent trend is

http://tinyurl.com/axrvo

where I "claimed" a certain formula to be more efficent than another.
That is:

=LOOKUP(9.99999999999999E+307,L:L)

is efficient compared to

=LOOKUP(2,1/ISNUMBER(L1:L1000),L1:L1000)

for the task of fetching the last numeric value from a range, consisting
of either manual entries or calculated values. There is no need to back
up this particular claim with any benchmarking or timing software. See
the discussion in that thread for why this should be so.

You also don't need to construct a temporal profile to claim that:

=SUMIF($C$4:$C$15,"S",$H$4:$H$*15)

is faster than:

=SUMPRODUCT(--($C$4:$C$15="S")*,$H$4:$H$15)

or

{=SUM(IF($C$4:$C$15="S",,$H$4:$H$15)}

for SumIf operates on range objects, not on array objects as the latter
two must, therefore faster.

To add just another example...

=LOOKUP(E2&F2,{"N","";"NN","";*"NY","D";"Y","";"YN","D";"YY",*"P"})

has a better temporal score than

=IF(OR(E2="",F2=""),"",IF(COUN*TIF(E2:F2,"Y")=2,"P",IF(OR(AND*(E2="Y",F2="N"),

AND(E2="N",F2="Y")),"D","")))

Do we need to profile them? I don't think so.

Some rules of thumb, derived from the knowledge of the behavior of the
functions (possible underlying algorithms the functions invoke), are:

1. Calculating on range objects is faster than calculating on array objects.

2. Lookup functions that resort to binary search are faster than lookup
functions which are set up to invoke linear search.

3. A formula with lesser number of function calls, all things being
equal, is better than one that invokes a multitude of functions.

4. The formulas without volatile functions are generally faster than the
formulas which invoke INDIRECT, OFFSET, etc. Also, OFFSET() is better
than INDIRECT(), couppled with ADDRESS().

5. Calculating on the relevant subranges is faster than on the whole range.

The foregoing list is by no means exhaustive.

>
> I'm just wondering how you test these for efficiency? Particularly, the
> speed of calculation. Do you use some type of benchmarking software or do
> you have VBA routines that do this?
>
> I'm always interested in improving my approaches to problems so any insight
> you can offer would be greatly appreciated.
>
> Biff

In a not neglible number of cases one needs to profile formulas. Charles
Williams's FastExcel meets this need nicely.

>
> "Aladin Akyurek" <[email protected]> wrote in message
> news:[email protected]...
>
>>Assuming that the data start at row 2 in column A and the values of
>>interest are text...
>>
>>Last text value:
>>
>>=LOOKUP(REPT("z",255),A2:A65536)
>>
>>Next to last value:
>>
>>=LOOKUP(REPT("z",255),A2:INDEX(A2:A65536,MATCH(REPT("z",255),A2:A65536)))
>>
>>This formulas would return a formula-blank (i.e., "") if such is the last
>>(or the next-to-last value).

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" <[email protected]> wrote in message
news:[email protected]...
>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
>

Hi!

> The issue in this thread wasn't one of efficiency, but correctness.

Well, let's see about that! <g>

Your formula uses logic that accounts for empty cells in the range, however,
the OP's sample data did not include any empty cells in the range. There was
also no mention of the possibility of empty cells in the range. So I based
my formulas on what was posted.

While it is probably not a good practice to assume things that are not
posted, it's good to be prepared for the possibilities.

But taking that into account and by the same token, then you also have done
what you want to correct me for.

Your formulas assume the values in the range are text. They may have been
formatted date/times. There may be both text and numeric values in the
range. If you're going to account for the possibility of empty cells in the
range then shouldn't you also account for mixed data types?

Gotcha! <g>

Thanks for the info regarding efficiency. I appreciate the contributions you
make here and have learned a great deal from you.

Biff

"Aladin Akyurek" <[email protected]> wrote in message
news:[email protected]...
> Biff wrote:
>> Hi!
>>
>> Frequently, you post options to others replies and claim that they are
>> more efficient.
>
> Biff,
>
> The issue in this thread wasn't one of efficiency, but correctness.
>
> Given:
>
> New York
> New Jersey
> Empty
> Empty
> Ohio
>
> in A2:A6
>
> =INDEX(A:A,MATCH(LOOKUP(REPT("z",255),A:A),A:A,0)-1)
>
> cannot capture the next-to-last text value. While, with missing -1
> added...
>
> =LOOKUP(REPT("z",255),A2:INDEX(A2:A65536,MATCH(REPT("z",255),A2:A65536)-1))
>
> will do so.
>
> Regarding efficiency issues, a recent trend is
>
> http://tinyurl.com/axrvo
>
> where I "claimed" a certain formula to be more efficent than another. That
> is:
>
> =LOOKUP(9.99999999999999E+307,L:L)
>
> is efficient compared to
>
> =LOOKUP(2,1/ISNUMBER(L1:L1000),L1:L1000)
>
> for the task of fetching the last numeric value from a range, consisting
> of either manual entries or calculated values. There is no need to back up
> this particular claim with any benchmarking or timing software. See the
> discussion in that thread for why this should be so.
>
> You also don't need to construct a temporal profile to claim that:
>
> =SUMIF($C$4:$C$15,"S",$H$4:$H$*15)
>
> is faster than:
>
> =SUMPRODUCT(--($C$4:$C$15="S")*,$H$4:$H$15)
>
> or
>
> {=SUM(IF($C$4:$C$15="S",,$H$4:$H$15)}
>
> for SumIf operates on range objects, not on array objects as the latter
> two must, therefore faster.
>
> To add just another example...
>
> =LOOKUP(E2&F2,{"N","";"NN","";*"NY","D";"Y","";"YN","D";"YY",*"P"})
>
> has a better temporal score than
>
> =IF(OR(E2="",F2=""),"",IF(COUN*TIF(E2:F2,"Y")=2,"P",IF(OR(AND*(E2="Y",F2="N"),
> AND(E2="N",F2="Y")),"D","")))
>
> Do we need to profile them? I don't think so.
>
> Some rules of thumb, derived from the knowledge of the behavior of the
> functions (possible underlying algorithms the functions invoke), are:
>
> 1. Calculating on range objects is faster than calculating on array
> objects.
>
> 2. Lookup functions that resort to binary search are faster than lookup
> functions which are set up to invoke linear search.
>
> 3. A formula with lesser number of function calls, all things being equal,
> is better than one that invokes a multitude of functions.
>
> 4. The formulas without volatile functions are generally faster than the
> formulas which invoke INDIRECT, OFFSET, etc. Also, OFFSET() is better than
> INDIRECT(), couppled with ADDRESS().
>
> 5. Calculating on the relevant subranges is faster than on the whole
> range.
>
> The foregoing list is by no means exhaustive.
>
>>
>> I'm just wondering how you test these for efficiency? Particularly, the
>> speed of calculation. Do you use some type of benchmarking software or do
>> you have VBA routines that do this?
>>
>> I'm always interested in improving my approaches to problems so any
>> insight you can offer would be greatly appreciated.
>>
>> Biff
>
> In a not neglible number of cases one needs to profile formulas. Charles
> Williams's FastExcel meets this need nicely.
>
>>
>> "Aladin Akyurek" <[email protected]> wrote in message
>> news:[email protected]...
>>
>>>Assuming that the data start at row 2 in column A and the values of
>>>interest are text...
>>>
>>>Last text value:
>>>
>>>=LOOKUP(REPT("z",255),A2:A65536)
>>>
>>>Next to last value:
>>>
>>>=LOOKUP(REPT("z",255),A2:INDEX(A2:A65536,MATCH(REPT("z",255),A2:A65536)))
>>>
>>>This formulas would return a formula-blank (i.e., "") if such is the last
>>>(or the next-to-last value).
>
>