Bernd P said:
To save one "IF" in our formula. Excel seems to lack an ARRAY
worksheet function. With such an internal function this would be
another approach...
To save one IF call at what cost?
My formula, reformatted:
=MAX(
IF(
COUNT(
MATCH(A3,A$2:A2,0)
),
LOOKUP(2,1/(A$2:A2=A3),C$2:C2),
0
)+B3,
0
)
5 function calls, 3 levels of nested function calls.
Your formula, reformatted:
=MAX(
$B3+
LOOKUP(
2,
1/($A3=myarray($A3,$A$2:$A2)),
myarray(0,C$2:C2)
),
0
)
4 function calls, 2 levels of nested function calls.
One fewer function call, but 2 of the remaining function calls are udf
calls. Guaranteed to be slower.
I could drop a function call from my formula, make it slower, but
still avoid udfs.
C2 formula remains =MAX(B2,0), but C3 formula becomes (reformatted)
=MAX(
IF(
COUNTIF(A$2:A2,A3),
LOOKUP(2,1/(A$2:A2=A3),C$2:C2),
0
)+B3,
0
)
4 function calls, 2 levels of nested function calls. The COUNTIF call
is usually going to be slower than COUNT(MATCH(..)) because the former
will always iterate through the entire A$2:A# range while the MATCH
call in the latter will only do so on the first instance of each new
Key, but will exit as soon as it finds the key thereafter.
If you like elegant formulas and lethargic recalculation, fine for
you, but others should be warned that udfs are ALWAYS slow and only
make sense when either absolutely necessary (providing functionality
that can't actually be provided by built-in functions) or when one udf
would do the work of at least 6 (yes, SIX) built-in functions.
Otherwise, the udf performance trade-off just isn't worth it.
