Auto calculation based on multiple criteria

Question

Hi, I need help to come out with a formula for column E in the image below. First, let me explain about the worksheet,

1. When Type (column B) = "H", the Grouping ID (column A) will auto-populate a number increasing by 1 to the number found in the cell above it.
2. When Type = "C", the Grouping ID keeps the same number found in the cell above it.
3. Column A is always sorted in ascending order.
4. There is no fixed number of "C" under a "H" in column B. Some "H"s have multiple "C" underneath them, and some have only one "C". Users can input as many "C" as they want under a specific "H".

Now, my challenge is, I need a formula to auto-calculate the average of all percentages in column D that are found under the same Grouping ID (column A), and write the results in column E on the same row as the respective "H" - example results shown in the red box below. To be honest, I really don't know if this is even possible in Excel. Hope someone can help. Thank you.

Answer 1

In E2:

=IF(B2="H", AVERAGEIFS($D$2:$D$10000, $A$2:$A$10000, A2), "")

Fill down.

Answer 2

Thank you so much HansV. This works perfectly!