Jerry W. Lewis wrote...
Some people are enamored of using -- to coerce logical arrays to numeric
arrays. IMHO, it obfuscates already complicated formulas like this one.
If you explicitly combine conditions using * for AND and + for OR, the
coercion occurs without all the extra --'s floating around.
[reformatted]
=SUMPRODUCT(
(
VALUE(
RIGHT(PosDeptNum,2)
)=$A7
)*
(
(PosStart<=G$6)*(PosEnd>=H$6)*PosHrs/40+
(PosEnd>=G$6)*(PosEnd<H$6)*PosHrs/40*(PosEnd-G$6)/(H$6-G$6-1)+
(PosStart>G$6)*(PosStart<H$6)*PosHrs/40*(H$6-PosStart)/(H$6-G$6-1)
)
is equivalent to your formula, involves fewer characters, and IMHO is
easier to follow.
It's *NOT* equivalent. The OP's formula is syntactically valid. Yours
isn't - it has unbalenced parentheses. Syntactically valid is never
equivalent to syntactically invalid.
As for easier to follow, one KEY aspect of the formula (with
unnecessary double unary minuses removed) is
(PosStart<=G$6)*(PosEnd>=H$6)*...+
(PosEnd>=G$6)*(PosEnd<H$6)*...+
(PosStart>G$6)*(PosStart<H$6)*...
which I'd rearrange for clarity as
(PosStart<=G$6)*(H$6<=PosEnd)*...+
(G$6<=PosEnd)*(PosEnd<H$6)*...+
(G$6<PosStart)*(PosStart<H$6)*...
indicates 3 states which, even under the assumption that G6 < H6
always,
would not be mutually exclusive if G6 < PosStart < PosEnd < H6, in
which
case *BOTH* the second and third states would apply, which is very
likely wrong.
It appears the OP wants the intersection of the periods G6 to H6 and
PosStart to PosEnd. Doing so requires more of a rewrite. Something like
=SUMPRODUCT((--RIGHT(PosDeptNum,2)=$A7)*(G$6<PosEnd)*(PosStart<H$6)*(
((H$6<=PosEnd)*H$6+(PosEnd<H$6)*PosEnd)-
((G$6<PosStart)*PosStart+(PosStart<=G$6)*G$6)-
(PosStart<=G$6)*(H$6<=PosEnd)
)*PosHrs/40/(H$6-G$6-1))
which is shorter than the OP's formula, syntactically valid, and, I
believe, corrects for the double counting which would occur when
PosStart to PosEnd falls properly within G6 to H6. It also provides
some garbage trapping by returning 0 when G6 is after PosEnd or H6 is
before PosStart.
Final comment. I agree that using too many --s is pointless. However,
it's better to coerce text to numeric using do-nothing arithmetic
operations than by using the VALUE function. There are too few levels
of nested function calls provided by Excel, so any opportunity to
eliminate unnecessary ones should be taken.
