Home |
Search |
Today's Posts |
#1
|
|||
|
|||
streamline SUMPRODUCT
I have 3 sumproducts that I am adding together and I would like to
combine them to one formula. Any suggestions? Here are the current formulae: SUMPRODUCT(--(VALUE(RIGHT(PosDeptNum,2))=$A7),--(PosStart<=G$6),--(PosEnd=H$6),PosHrs/40) SUMPRODUCT(--(VALUE(RIGHT(PosDeptNum,2))=$A7),--(PosEnd=G$6),--(PosEnd<H$6),PosHrs/40*(PosEnd-G$6)/(H$6-G$6-1)) SUMPRODUCT(--(VALUE(RIGHT(PosDeptNum,2))=$A7),--(PosStartG$6),--(PosStart<H$6),PosHrs/40*(H$6-PosStart)/(H$6-G$6-1)) TIA! |
#2
|
|||
|
|||
Got it!
=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)+--(PosStartG$6)*--(PosStart<H$6)*PosHrs/40* (H$6-PosStart)/(H$6-G$6-1)) |
#3
|
|||
|
|||
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. =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) +(PosStartG$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. Jerry gpie wrote: Got it! =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)+--(PosStartG$6)*--(PosStart<H$6)*PosHrs/40* (H$6-PosStart)/(H$6-G$6-1)) |
#4
|
|||
|
|||
You have just cleared up something that has been confusing me for
months! thanks! I didn't realize that "--" was only necessary when using "*" instead of ",". This is much better! |
#5
|
|||
|
|||
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)+ (PosStartG$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)*...+ (PosStartG$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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct | Excel Worksheet Functions | |||
sumproduct causing memory errors? | Excel Worksheet Functions | |||
Can I reference =, <, or > sign in SUMPRODUCT | Excel Discussion (Misc queries) | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |