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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

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,126,939
Messages
5,621,723
Members
415,853
Latest member
Newlife72

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