Weeks of Supply Calculation with Quarter Breaks

[patrick99]

Hello

I am trying to modify the formula in this THREAD to account for a quarter total. My data spans a year in weekly buckets with a quarter total every 13 weeks.

Original formula:

=(SUMPRODUCT(--(SUM(B4)>=SUBTOTAL(9,OFFSET(C3:$AB3,,,,COLUMN(C3:$AB3)-COLUMN(C3)+1))))+ABS(LOOKUP(0,(SUBTOTAL(9,OFFSET(C3:$AB3,,,,COLUMN(C3:$AB3)-COLUMN(C3)+1))-SUM(B4)-C3:$AB3)/C3:$AB3)))

Updated formula:

=IF(13-B2>=SUMPRODUCT(--(SUM(B4)>=SUBTOTAL(9,OFFSET(C3:$AB3,,,,COLUMN(C3:$AB3)-COLUMN(C3)+1))))+IF(ABS(LOOKUP(0,(SUBTOTAL(9,OFFSET(C3:$AB3,,,,COLUMN(C3:$AB3)-COLUMN(C3)+1))-SUM(B4)-C3:$AB3)/C3:$AB3))=1,0,ABS(LOOKUP(0,(SUBTOTAL(9,OFFSET(C3:$AB3,,,,COLUMN(C3:$AB3)-COLUMN(C3)+1))-SUM(B4)-C3:$AB3)/C3:$AB3))),SUMPRODUCT(--(SUM(B4)>=SUBTOTAL(9,OFFSET(C3:$AB3,,,,COLUMN(C3:$AB3)-COLUMN(C3)+1))))+IF(ABS(LOOKUP(0,(SUBTOTAL(9,OFFSET(C3:$AB3,,,,COLUMN(C3:$AB3)-COLUMN(C3)+1))-SUM(B4)-C3:$AB3)/C3:$AB3))=1,0,ABS(LOOKUP(0,(SUBTOTAL(9,OFFSET(C3:$AB3,,,,COLUMN(C3:$AB3)-COLUMN(C3)+1))-SUM(B4)-C3:$AB3)/C3:$AB3))),SUMPRODUCT(--(SUM(B4)>=SUBTOTAL(9,OFFSET(C3:$AB3,,,,COLUMN(C3:$AB3)-COLUMN(C3)+1))))+IF(ABS(LOOKUP(0,(SUBTOTAL(9,OFFSET(C3:$AB3,,,,COLUMN(C3:$AB3)-COLUMN(C3)+1))-SUM(B4)-C3:$AB3)/C3:$AB3))=1,0,ABS(LOOKUP(0,(SUBTOTAL(9,OFFSET(C3:$AB3,,,,COLUMN(C3:$AB3)-COLUMN(C3)+1))-SUM(B4)-C3:$AB3)/C3:$AB3)))-1)

The original formula overstated the integer part by 1 in some cases.
The updated formula the first part (integer) of the formula calculates correctly, I think due to my quarter total being a "SUBTOTAL" which is ignored. The second half (fractional) is incorrect.
Is there an easier way to do this? This formula will need to be inserted with VBA and is awfully long.
I could probably live with the incorrect fractional part since it doesn't always happen and my example values are unlikely.