Combine dissimilar spill arrays to calculate monthly contributions
I have data that is massaged into summary tables that expand off the source data, which only lists the start and end date for example, so these summaries expand the months and calculates the avg hours per month for a given subtask. Then, we have a summary of each person contributions by subtask. Now, what I am trying to calculate is the monthly contribution by person by month. We know the hours spent on each subtask per month, and the hours spent per subtask by person, but I'm unsure how to go about combining these datasets to get "Monthly Contribution Per Person". I'll add markdowns at the bottom.
In the image, outlines denote the range is a spill array. The pinkish color is where I would like to calculate the values by the cols and rows, using the information on the side. I'll give a zoomed in version as well if this is hard to read. But this gives the fuller picture.
In the spill arrays, I can adjust the formulas to be % of totals. (Ie: the Person1's contribution can be calculated to show (2 / 16) = (12.5% * 16) = 2 for 7/1/24 for the first example (c23), but apply this logic through all subtasks for Person1 for each month. For the same example, if we look at subtask 1.7, Person1 did .50 hours, therefore (.5/6.75) * .56 = monthly contribution for applicable months .
The math is very simple, where Person 1's subtask % * to the monthly amount would be the value, but isolating this matrix without the subtasks is where I'm confused. I've tried using:
=MAKEARRAY(ROWS(P3#),COLUMNS(R1#),LAMBDA(r,c, SUM( C3# * --(C1#=INDEX(R1#,1,c))*--(C21#=INDEX(P3#,r)))))
I tried Sumproduct but the differently sized arrays give an error.
Which results in #N/A!, but I'm sure because there isn't a consideration made for the subtask.
| Subtask | Total | 7/1/2024 | 8/1/2024 | 9/1/2024 | 10/1/2024 | 11/1/2024 | 12/1/2024 | 1/1/2025 | 2/1/2025 | 3/1/2025 | 4/1/2025 | 5/1/2025 | 6/1/2025 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 528.25 | 135.48 | 63.46 | 36.08 | 32.58 | 32.58 | 32.58 | 32.58 | 32.58 | 32.58 | 32.58 | 32.58 | 32.58 | |
| 1.1 | 16.00 | 16.00 | - | - | - | - | - | - | - | - | - | - | - |
| 1.2 | 7.50 | 7.50 | - | - | - | - | - | - | - | - | - | - | - |
| 1.3 | 4.25 | 4.25 | - | - | - | - | - | - | - | - | - | - | - |
| 1.4 | 27.00 | 27.00 | - | - | - | - | - | - | - | - | - | - | - |
| 1.5 | 15.00 | 15.00 | - | - | - | - | - | - | - | - | - | - | - |
| 1.6 | 13.50 | 13.50 | - | - | - | - | - | - | - | - | - | - | - |
| 1.7 | 6.75 | 0.56 | 0.56 | 0.56 | 0.56 | 0.56 | 0.56 | 0.56 | 0.56 | 0.56 | 0.56 | 0.56 | 0.56 |
| 1.8 | 140.00 | 11.67 | 11.67 | 11.67 | 11.67 | 11.67 | 11.67 | 11.67 | 11.67 | 11.67 | 11.67 | 11.67 | 11.67 |
| 2.1 | 54.00 | 4.50 | 4.50 | 4.50 | 4.50 | 4.50 | 4.50 | 4.50 | 4.50 | 4.50 | 4.50 | 4.50 | 4.50 |
| 2.2 | 13.50 | - | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 |
| 2.3 | 13.50 | 1.13 | 1.13 | 1.13 | 1.13 | 1.13 | 1.13 | 1.13 | 1.13 | 1.13 | 1.13 | 1.13 | 1.13 |
| 2.4 | 162.00 | 13.50 | 13.50 | 13.50 | 13.50 | 13.50 | 13.50 | 13.50 | 13.50 | 13.50 | 13.50 | 13.50 | 13.50 |
| 3.1 | 3.75 | 3.75 | - | - | - | - | - | - | - | - | - | - | - |
| 3.2 | 34.25 | 17.13 | 17.13 | - | - | - | - | - | - | - | - | - | - |
| 3.3 | 7.50 | - | 7.50 | - | - | - | - | - | - | - | - | - | - |
| 3.4 | 7.00 | - | 3.50 | 3.50 | - | - | - | - | - | - | - | - | - |
| 4.1 | 2.75 | - | 2.75 | - | - | - | - | - | - | - | - | - | - |
| Subtask | Person1 | Person2 | Person3 | Person4 | Person5 | Person6 | Person7 | Person8 | Person9 | Person10 | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1.1 | 100.0% | 12.5% | 0.0% | 12.5% | 0.0% | 0.0% | 25.0% | 0.0% | 50.0% | 0.0% | 0.0% |
| 1.2 | 100.0% | 0.0% | 0.0% | 0.0% | 0.0% | 0.0% | 6.7% | 0.0% | 40.0% | 0.0% | 53.3% |
| 1.3 | 100.0% | 0.0% | 0.0% | 5.9% | 0.0% | 0.0% | 23.5% | 0.0% | 70.6% | 0.0% | 0.0% |
| 1.4 | 100.0% | 7.4% | 7.4% | 7.4% | 7.4% | 7.4% | 9.3% | 9.3% | 14.8% | 14.8% | 14.8% |
| 1.5 | 100.0% | 0.0% | 0.0% | 6.7% | 0.0% | 0.0% | 40.0% | 0.0% | 53.3% | 0.0% | 0.0% |
| 1.6 | 100.0% | 7.4% | 7.4% | 7.4% | 7.4% | 7.4% | 9.3% | 9.3% | 14.8% | 14.8% | 14.8% |
| 1.7 | 100.0% | 7.4% | 7.4% | 7.4% | 7.4% | 7.4% | 9.3% | 9.3% | 14.8% | 14.8% | 14.8% |
| 1.8 | 100.0% | 0.0% | 0.0% | 42.9% | 0.0% | 0.0% | 57.1% | 0.0% | 0.0% | 0.0% | 0.0% |
| 2.1 | 100.0% | 7.4% | 7.4% | 7.4% | 7.4% | 7.4% | 9.3% | 9.3% | 14.8% | 14.8% | 14.8% |
| 2.2 | 100.0% | 7.4% | 7.4% | 7.4% | 7.4% | 7.4% | 9.3% | 9.3% | 14.8% | 14.8% | 14.8% |
| 2.3 | 100.0% | 7.4% | 7.4% | 7.4% | 7.4% | 7.4% | 9.3% | 9.3% | 14.8% | 14.8% | 14.8% |
| 2.4 | 100.0% | 7.4% | 7.4% | 7.4% | 7.4% | 7.4% | 9.3% | 9.3% | 14.8% | 14.8% | 14.8% |
| 3.1 | 100.0% | 0.0% | 0.0% | 6.7% | 0.0% | 0.0% | 13.3% | 0.0% | 53.3% | 0.0% | 26.7% |
| 3.2 | 100.0% | 0.0% | 0.0% | 0.7% | 0.0% | 0.0% | 11.7% | 0.0% | 29.2% | 0.0% | 58.4% |
| 3.3 | 100.0% | 0.0% | 0.0% | 6.7% | 0.0% | 0.0% | 13.3% | 0.0% | 26.7% | 0.0% | 53.3% |
| 3.4 | 100.0% | 0.0% | 0.0% | 14.3% | 0.0% | 0.0% | 28.6% | 0.0% | 57.1% | 0.0% | 0.0% |
| 4.1 | 100.0% | 0.0% | 0.0% | 9.1% | 0.0% | 0.0% | 18.2% | 0.0% | 72.7% | 0.0% | 0.0% |
| Subtask | Person1 | Person2 | Person3 | Person4 | Person5 | Person6 | Person7 | Person8 | Person9 | Person10 | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1.1 | 16.00 | 2.00 | - | 2.00 | - | - | 4.00 | - | 8.00 | - | - |
| 1.2 | 7.50 | - | - | - | - | - | 0.50 | - | 3.00 | - | 4.00 |
| 1.3 | 4.25 | - | - | 0.25 | - | - | 1.00 | - | 3.00 | - | - |
| 1.4 | 27.00 | 2.00 | 2.00 | 2.00 | 2.00 | 2.00 | 2.50 | 2.50 | 4.00 | 4.00 | 4.00 |
| 1.5 | 15.00 | - | - | 1.00 | - | - | 6.00 | - | 8.00 | - | - |
| 1.6 | 13.50 | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 | 1.25 | 1.25 | 2.00 | 2.00 | 2.00 |
| 1.7 | 6.75 | 0.50 | 0.50 | 0.50 | 0.50 | 0.50 | 0.63 | 0.63 | 1.00 | 1.00 | 1.00 |
| 1.8 | 140.00 | - | - | 60.00 | - | - | 80.00 | - | - | - | - |
| 2.1 | 54.00 | 4.00 | 4.00 | 4.00 | 4.00 | 4.00 | 5.00 | 5.00 | 8.00 | 8.00 | 8.00 |
| 2.2 | 13.50 | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 | 1.25 | 1.25 | 2.00 | 2.00 | 2.00 |
| 2.3 | 13.50 | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 | 1.25 | 1.25 | 2.00 | 2.00 | 2.00 |
| 2.4 | 162.00 | 12.00 | 12.00 | 12.00 | 12.00 | 12.00 | 15.00 | 15.00 | 24.00 | 24.00 | 24.00 |
| 3.1 | 3.75 | - | - | 0.25 | - | - | 0.50 | - | 2.00 | - | 1.00 |
| 3.2 | 34.25 | - | - | 0.25 | - | - | 4.00 | - | 10.00 | - | 20.00 |
| 3.3 | 7.50 | - | - | 0.50 | - | - | 1.00 | - | 2.00 | - | 4.00 |
| 3.4 | 7.00 | - | - | 1.00 | - | - | 2.00 | - | 4.00 | - | - |
| 4.1 | 2.75 | - | - | 0.25 | - | - | 0.50 | - | 2.00 | - | - |
For expected outcome, the ultimate goal is to figure the assumed hours someone worked for each month. So if we know subtask 1.1 took 16 hours in 7/1/24, all of person1's 2 hours would be in 7/1/24. If this subtask was split over 8/1/24, person1 would have 1 hour in 7/1/24 and 1 hour in 8/1/24. Split over 4 months it would be .5 per month. etc. but the sum of this logic for all subtasks for that month.
In this image, I show person1 and person2 done manually where I determined their monthly hours by taking their contribution of each subtask for each month.
Here is the solution:
Fill out this formula from S3 to right:
[S3]=REDUCE(0,SEQUENCE(ROWS(A3:A19)),LAMBDA(a,x,a+TRANSPOSE(INDEX($C$23:$L$39,x))*
INDEX(C3:C19,x)/INDEX($B3:$B19,x)))
If any task can be 0 hours, use this formula for the full range of time:
[S3]=LET(tpt,C3:N19,tpp,C23:L39,tt,B3:B19,
MAKEARRAY(COLUMNS(tpp),COLUMNS(tpt),
LAMBDA(r,c,SUM(IF(tt=0,0,INDEX(tpp,0,r)*INDEX(tpt,0,c)/tt)))))
UPDATE
The simpler solution is here:
=MMULT(--TRANSPOSE(C23:L39);C3:N19/IF(B3:B19=0;1;B3:B19))
- Constrain a Specman list so it doesn't have identical values in consecutive elements
- How to type cast a list of uint to a list of vr_ahb_data in Specman?
- Static fields/methods in e
- What is the difference between deep_copy and gen keeping in Specman?
- Specman UVM: What is the difference between write_reg { .field == 2;}; and write_reg_fields?
- Does Specman support optional parameters to a method?
- Specman e: When colon equal sign ":=" should be used?
- Specman e: Is there a way to know how many values there is in an enumerated type?
- Specman e error: No match for file when using "for each line in file"
- How to run e file one by one? Not in parallel test
- Specman/e constraint (for each in) iteration
- Specman e: How driver's items queue can be locked from a sequence?
- Specman e: How to print variable's address?
- Specman e: "keep type .. is a" fails to refine the type of a field
- Specman soft select on variable, decimal vs. hexadecimal values
- Specman e: How to print a pointer to a struct?
- Specman e: Is there a way to print unit instance name?
- Specman e: simultaneous events error
- Specman e coverage: ignored values appear in the coverage statistics
- Specman e: How a sequence should be started when gen_and_start_main constrained to FALSE?
- Specman/e list of lists (multidimensional array)
- Does Specman e have struct constructor?
- Specman e: How the predefined sequence.item should be used?
- Specman e: define-as-computed macro error
- Specman e UVM: Why to inherit from uvm_* units?
- Specman e: A sequence drives its BFM also its MAIN was not defined in a test
- e HVL (IEEE 1647): expect expression fails unexpectedly
- Specman e subtyping: How to refer to FALSE value of conditional field in when/extend subtyping?
- e HVL (IEEE 1647): How to set 'X' value?
- Difference between declaring an event that is sensitive to a simple_port value and event_port