# Weeks of Supply Calculation with Quarter Breaks

#### patrick99

##### New Member
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:
VBA Code:
``=(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:
VBA Code:
``=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.

#### Attachments

• fwdweeks.jpg
111.6 KB · Views: 4

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Replies
2
Views
82
Replies
2
Views
187
Replies
6
Views
59
Replies
4
Views
175
Replies
0
Views
47