Weighted average help needed

[Bren4242]

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

 

[NateSC]

Does Column H have equations? If so, what is the equation in H5?

 

[Bren4242]

Does Column H have equations? If so, what is the equation in H5?

In H5 is "=OFFSET($B$2,,COUNTIF(B5:F5,"<="&$A$17)-1)", copied down to H14,
H15 = average(H5:H14)

 

[ExceLoki]

have you tried something like this?

=average(OFFSET($B$2,,COUNTIF(B5:F5,"<="&$A17)-1),OFFSET($B$2,,COUNTIF(B6:F6,"<="&$A17)-1),OFFSET($B$2,,COUNTIF(B7:F7,"<="&$A17)-1),OFFSET($B$2,,COUNTIF(B8:F8,"<="&$A17)-1),OFFSET($B$2,,COUNTIF(B9:F9,"<="&$A17)-1),OFFSET($B$2,,COUNTIF(B10:F10,"<="&A17)-1),OFFSET($B$2,,COUNTIF(B11:F11,"<="&$A17)-1),OFFSET($B$2,,COUNTIF(B12:F12,"<="&$A17)-1),OFFSET($B$2,,COUNTIF(B13:F13,"<="&$A17)-1),OFFSET($B$2,,COUNTIF(B14:F14,"<="&$A17)-1))