Weeks of Supply Calculation with Quarter Breaks

patrick99

New Member
Joined
Aug 24, 2020
Messages
1
Office Version
  1. 2013
Platform
  1. Windows
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
    fwdweeks.jpg
    111.6 KB · Views: 4

Some videos you may like

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.

Watch MrExcel Video

Forum statistics

Threads
1,118,186
Messages
5,570,750
Members
412,340
Latest member
nikitesh95
Top