Average value of a parent in an hierarchy

I have primarily four columns with the first three in Hierarchy

XX-->A-->B-->C-->D

XX is a parent with just about 4 values, in A

A is the parent with around a 100 attributes in it

B is the child with about 1-4 values for each A

C is a measure value - pecentage

D is the status based off that percentage value in C, such as >95 being good, less than 90 being poor etc.


I want a separate tab that just lists the parent A and its average of all B's with the status next to it.


I have attached a sample sheet as an example. With regions as the parent A. So
East A a.1 100 Good
East A a.2 70 Poor
West A a.3 90 Average
West A a.2 99 Good

I would like the result to be,

East A 85 poor
West A 94.5 Average

I'd really appreciate any help i can get with respect to this. Thanks guys.

Regards,
Nik

Hi,

Please find the sample file attached. You may use sheet4 in the file.

Also, the column names are different that what I mentioned in the problem, and I also gave an example of the output I am looking for, in the same sheet.

Thank you.

Nik