Splitting Budget Hours per Workstream by Month

Hi,

I'm really struggling with this one:

I have 2 tables, one for the budget per workstream, the other for total chargeable hours per month.

If the workstream has a YES, that means its chargeable. If it is blank it is not chargeable.

Taking Workstream 1 for example, it will have time in September - February but not March. So we need to split 400 hours over Sep-Feb. However, we can't do this on an even basis.

In September all 6 workstreams will be charging time up to a maximum of 40 hours (sum of all workstreams). So workstream 1 will have the highest proportion of time (out of 40) in September but it will not be allocated it all.

Hopefully this makes sense and someone can provide a formula which will help me split this.

	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Total Hours
Workstream 1	YES	YES	YES	YES	YES	YES		400
Workstream 2	YES	YES	YES	YES				200
Workstream 3	YES	YES	YES	YES				200
Workstream 4	YES	YES						50
Workstream 5	YES	YES						21
Workstream 6	YES	YES	YES	YES	YES	YES	YES	100
Total Hours								971

	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Total Hours
Total Hours	40	160	160	80	180	180	171	971

Formulas and Functions

Patrick2788

Sep 14, 2023

Damian510

I'm not completely clear on how you want to handle the allocation but here's a start:

=LET(
    cols, ROWS(Workstreams),
    matrix, SWITCH(Workstreams, "Yes", 1, 0),
    multiplier, SEQUENCE(, cols, 1, 0),
    monthly_totals, MMULT(multiplier, matrix),
    Allocated, TotalHours / monthly_totals,
    Allocated * matrix
)

Allocation.xlsx14 KB

5 Replies

Patrick2788
Silver Contributor
Sep 14, 2023
Damian510
I'm not completely clear on how you want to handle the allocation but here's a start:

=LET( cols, ROWS(Workstreams), matrix, SWITCH(Workstreams, "Yes", 1, 0), multiplier, SEQUENCE(, cols, 1, 0), monthly_totals, MMULT(multiplier, matrix), Allocated, TotalHours / monthly_totals, Allocated * matrix )

Allocation.xlsx14 KB
- Damian510
  Copper Contributor
  Sep 14, 2023
  Patrick2788
  
  For September, workstream 1 should have the most hours (based on the total hours for each workstream). So Workstream 1 has 400 hours over Sep-Feb (6 months) out of the total 971 hours. So roughly of the 40 hours in september, Workstream 1 should have 42% (400/971) of 40. But the issue is doing this for every month (Sep/Feb) will not total to 400.
  
  It's really hard to explain lol.
  - Patrick2788
    Silver Contributor
    Sep 14, 2023
    It sounds like it has some nuances to the caculations. It might help if you're able to post a table of 'desired results'.

Forum Discussion

Splitting Budget Hours per Workstream by Month

5 Replies

Resources