3 dimensional aggregated Average

[audunoslo]

Hi,

I am working with survey feedback, and want to aggregate the reponse

Questions hierarchy.
Main category 1 Sub category 1 Question 1
Main category 1 Sub category 1 Question 2
Main category 1 Sub category 1 Question 3
Main category 1 Sub category 2 Question 4
Main category 1 Sub category 2 Question 5
Main category 2 Sub category 3 Question 6
Main category 2 Sub category 3 Question 7
Main category 2 Sub category 4 Question 8
Main category 2 Sub category 4 Question 9

The response form is in the form
Respondent 1 question 1 xxxx
Respondent 1 question 2 xxxx
Respondent 1 question 3 xxxx
....
Respondent 2 question 1 xxxx
Respondent 2 question 2 xxxx
Respondent 2 question 3 xxxx
....
...
...

I want to be able to sum up the average answer at each question, then find the average between each the questions under the sub category. Before finding the average of the sub categories within each Main Category.

 

[audunoslo]

Forgot the last part of the challenge - There is multiple (Employees belonging to a company) Respondent 1, Respondent 2, etc. where the average among them needs to be calculated first.

 

[Aladin Akyurek]

Post a small sample of the scaled-down problem (reduce number of questions, reduce subcategories, and main categories).

 

[audunoslo]

Thank you for your response. The attachment will lead to a file with all relevant tables used in process. The original excel workbook has only one table per sheet The challenge is to create the averages of the averages average without copy pasting tables. For example table 3 and 4. The problem gets more challenging when the companies are split into categories, and a new level of average is created.

https://www.dropbox.com/s/o8k0l57xd745o46/ExcelQuestion-average of average.xlsx?dl=0

I strongly appreciate all help I can get. It is my understanding that the problem can be solved with aggregation, but I am struggling with how to add all the layers required.