3 dimensional aggregated Average

audunoslo

New Member
Joined
Jun 28, 2015
Messages
3
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.
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

audunoslo

New Member
Joined
Jun 28, 2015
Messages
3
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
Post a small sample of the scaled-down problem (reduce number of questions, reduce subcategories, and main categories).
 

audunoslo

New Member
Joined
Jun 28, 2015
Messages
3
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,988
Messages
5,627,996
Members
416,286
Latest member
ko15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top