Distributing Values Over Time

Question

In this example, I have two columns of data: Date and Quantity.

Example: (See Screenshot Link)

What I'd like to be able to do, with an excel formula (if possible), is 'level load' these quantities and spread them out, as evenly as possible, over the given time frame while preserving whole numbers. The ideal day of each value would correspond to Thursday of the given week.

Desired result: (See Screenshot Link)

Answer 1

It's possible, yet tough. Here is my two cents (and I might have been overthinking this by a mile):

• Assuming access to the newest BETA-functions (or an alternative mentioned further down);
• Months follow one another in order e.g.: may > june > july etc.

Formula in E2:

=LET(A,SEQUENCE(EOMONTH(A5,0)-A3,,A3),B,FILTER(A,(WEEKDAY(A)=5)*((DAY(A)/7)-0.1<VLOOKUP(A,A3:B5,2))),C,BYROW(B,LAMBDA(x,SUM(--(MONTH(B)=MONTH(x))))),D,VLOOKUP(B,A3:B5,2),VSTACK(A2:B2,HSTACK(B,FLOOR(D/C,1)+TEXTSPLIT(CONCAT(TAKE(UNIQUE(HSTACK(MONTH(B),RIGHT("0|0|0|0|0|"&REPT("1|",MOD(D,C)),C*2))),,-1)),,"|",1))))

Or (just as verbose, but no TEXTSPLIT()):

=LET(A,SEQUENCE(EOMONTH(A5,0)-A3,,A3),B,FILTER(A,(WEEKDAY(A)=5)*((DAY(A)/7)-0.1<VLOOKUP(A,A3:B5,2))),C,BYROW(B,LAMBDA(x,SUM(--(MONTH(B)=MONTH(x))))),D,VLOOKUP(B,A3:B5,2),VSTACK(A2:B2,HSTACK(B,FLOOR(D/C,1)+MID(CONCAT(TAKE(UNIQUE(HSTACK(MONTH(B),RIGHT("00000"&REPT("1",MOD(D,C)),C))),,-1)),SEQUENCE(COUNT(B)),1))))

As you can tell, this is a stretch (only proving @Scott's point in the comment that this may be easier through VBA).

---

If you have ms365 but no access to the BETA-functions, you can use:

=LET(A,SEQUENCE(EOMONTH(A5,0)-A3,,A3),B,FILTER(A,(WEEKDAY(A)=5)*((DAY(A)/7)-0.1<VLOOKUP(A,A3:B5,2))),C,BYROW(B,LAMBDA(x,SUM(--(MONTH(B)=MONTH(x))))),D,VLOOKUP(B,A3:B5,2),CHOOSE({1,2},B,FLOOR(D/C,1)+MID(CONCAT(INDEX(UNIQUE(CHOOSE({1,2},MONTH(B),RIGHT("00000"&REPT("1",MOD(D,C)),C))),,2)),SEQUENCE(COUNT(B)),1)))

You'd just need to add titles yourself (since there is no VSTACK()).

---

---