Forum Discussion
Solved
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 ch...
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 )
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
)
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.
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.
- It sounds like it has some nuances to the caculations. It might help if you're able to post a table of 'desired results'.
I've relooked at it again, and it's just not possible to do logically. I'm going to close the request but thanks for your help!
- You're welcome!