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(R EPT("z",255),A2:A65536)))
This formulas would return a formula-blank (i.e., "") if such is the last
(or the next-to-last value).