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" wrote in message
...
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(REP T("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), a
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" wrote in message
...
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).