Long long formula not calc'ing all steps

**jvautour** · 10-01-2009, 10:12 AM

I have a very very very long formula in which I'm getting no errors, but the calculations aren't all being done. I'm not sure if I need to move or add parentheses. Below is the formula. Does anyone see why the bold parts of the formula aren't working?

=((IF(J$4-$D5<365.25,(SUMPRODUCT(--(A!$A$2:$A$4784=B!$A8),--(A!$B$2:$B$4784=B!J$4),A!$Q$2:$Q$4784))*0.0192,IF(J$4-$D5<3653,(SUMPRODUCT(--(A!$A$2:$A$4784=B!$A8),--(A!$B$2:$B$4784=B!J$4),A!$Q$2:$Q$4784))*0.0385,IF(J$4-$D5>=3653,(SUMPRODUCT(--(A!$A$2:$A$4784=B!$A8),--(A!$B$2:$B$4784=B!J$4),A!$Q$2:$Q$4784)*0.0577)))))*AND(IF(J$4-$D5>0,((IF(J$4-$D5<365.25,(SUMPRODUCT(--(A!$A$2:$A$4784=B!$A8),--(A!$B$2:$B$4784=B!J$4),A!$Q$2:$Q$4784)*0.0192),IF(J$4-$D5<3653,(SUMPRODUCT(--(A!$A$2:$A$4784=B!$A23),--(A!$B$2:$B$4784=B!J$4),A!$Q$2:$Q$4784)*0.0385),IF(J$4-$D5>=3653,(SUMPRODUCT(--(A!$A$2:$A$4784=B!$A23),--(A!$B$2:$B$4784=B!J$4),A!$Q$2:$Q$4784))*0.0577,0)))))))*AND(IF(J$4-$E5<0,((IF(J$4-$D5<365.25,(SUMPRODUCT(--(A!$A$2:$A$4784=B!$A23),--(A!$B$2:$B$4784=B!J$4),A!$Q$2:$Q$4784)*0.0192),IF(J$4-$D5<3653,(SUMPRODUCT(--(A!$A$2:$A$4784=B!$A23),--(A!$B$2:$B$4784=B!J$4),A!$Q$2:$Q$4784)*0.0385),IF(J$4-$D5>=3653,(SUMPRODUCT(--(A!$A$2:$A$4784=B!$A30),--(A!$B$2:$B$4784=B!J$4),A!$Q$2:$Q$4784)*0.0577),0)))))))+H5-J5)

**NBVC** · 10-01-2009, 10:22 AM

Kind of confusing, yes!

If you are using the Same Sumproduct() for all conditions and only the multiplier is differenct, then put the Sumproduct before the IF() and then * between, and the AND() function comes before the conditions and gets enclosed in brackets with conditions separated by commas...

e.g.

=SUMPRODUCT(--(A!$A$2:$A$4784=B!$A8),--(A!$B$2:$B$4784=B!J$4),A!$Q$2:$Q$4784)*IF(J$4-$D5<365.25,0.0192,IF(AND(...,...),....,IF(....