Move calculated means

  • Thread starter Thread starter Charles Deng
  • Start date Start date
C

Charles Deng

I have a huge data set which contains variables of Dept,
Section, q1 to q5. (q1 to q5 are five questions asked in
a survey). Now I need to calculate means for each question
by section. Then I need to move these means to the other
colums. The data set format is like this:


Dept Section q1 q2 q3 q4 q5
ACC 100 2 1 3 4 1
ACC 100 3 2 4 2 1
ACC 200 3 2 4 3 2
ACC 200 3 2 3 2 1
BUS 100 4 4 3 2 1
BUS 100 2 2 3 2 1
BUS 200 4 3 2 1 2

I need to calculate the mean of respondense to these five
questions for each section within department. Then I need
to move these means for each section (with dept and
section names) to new columns.

After I moved these means to new columns, I need to sort
the mean of each section by questions within department,
for example, for question 1 (q1), there are mean for ACC
100, mean for ACC 200, mean for BUS 100, and mean for BUS
200. I need to sort these means within each department.
Can I do these using Excel? I mean I don't want to do
these manually. Thanks a lot.

Charles
 
i j k l m
n p
q1 q2 q3 q4 q5
ACC 100 2.5 1.5 3.5 3 1
ACC 200 3 2 3.5 2.5 1.5
BUS 100 3 3 3 2 1
BUS 200 4 3 2 1 2

assuming your data starts at a2

=SUMPRODUCT(($A$2:$A$8=$I2)*($B$2:$B$8=$J2)*(C$2:C$8))/SUMPRODUCT(($A$2:$A$8=$I2)*($B$2:$B$8=$J2))

will provide the table you see above. You will be required to provide the
unique listing of dept and sections in columns I and J (look at advance
filters to produce the unique list)

Lance
 
Back
Top