Apologies if I've not titled this correctly, not 100% sure what to call this kind of request TBH.
I need to track the average completion percentage for a collection of documents which are at different stages of completion. Image attached to illustrate.
I need to complete the Average % table at the bottom (rows 17-26), which gives the average % complete for all the documents based on which stage they are in by the given date.
Example for the first set at 17/12/2021 (dd/mm/yyyy, 17 December 2021):
I marked in orange the stage each document was/would be in at the given date (17-Dec), eg Doc2 was in Stage 3 at that time, Doc10 was in Stage 1
Each stage has a weighted percentage shown in rows 1-2, so Doc2 would be 50% complete, Doc10 would be 10%.
Column H shows the completion percentage for each document as it would've been at 17-Dec, which averages to 22%.
The question: What formula can I use in the table in range B17:B26 to give me the average for at each of the period-end dates? The quick/dirty solution might be to just keep adding columns similar to column H and calculate it from there, but I'm hoping there is a more elegant solution
I need to track the average completion percentage for a collection of documents which are at different stages of completion. Image attached to illustrate.
I need to complete the Average % table at the bottom (rows 17-26), which gives the average % complete for all the documents based on which stage they are in by the given date.
Example for the first set at 17/12/2021 (dd/mm/yyyy, 17 December 2021):
I marked in orange the stage each document was/would be in at the given date (17-Dec), eg Doc2 was in Stage 3 at that time, Doc10 was in Stage 1
Each stage has a weighted percentage shown in rows 1-2, so Doc2 would be 50% complete, Doc10 would be 10%.
Column H shows the completion percentage for each document as it would've been at 17-Dec, which averages to 22%.
The question: What formula can I use in the table in range B17:B26 to give me the average for at each of the period-end dates? The quick/dirty solution might be to just keep adding columns similar to column H and calculate it from there, but I'm hoping there is a more elegant solution