Collated Survey Analysis Finding Average

[Debbie D.]

Hi all the following table shows the number of instances a manager has
received a score of 1 to 7 against a particular survey question.
Unfortunately, the survey package used has automatically collated the
instances that a manager has scored a particular score so I can’t use an
average formula.

Basically, what I’m trying to do is to work out the score out of 7 for each
of the answers. So, below you can see that this individual has had zero
people scoring them as a ‘1’, 2 people scoring them as a ‘2’, 1 person
scoring them as a ‘3’ – etc etc.

I need to find the average score for the individual, bearing in mind the
number of times a particular score has been selected. To do this ‘longhand’
would be to find the average of “2,2,3,5,5,6,7,7,7,7,7,7,7,7,7,7†– which is
6! However, I can’t think of any way to write this as a formula in Excel –
which I desperately need to do as I have about 20 of these spreadsheets to
work through!!


Thanks for taking the time to look and any help appreciated.
Deborah D. (UK)


My manager is effective at planning (i.e. deciding what to do, when,
policies, course of action etc)


1 2 3 4 5 6 7
0 2 1 0 2 1 10

 

[ryguy7272]

Are you saying that “2,2,3,5,5,6,7,7,7,7,7,7,7,7,7,7†is all in one cell? If
so, select your cells, Data > Text to columns > Delimited > Check Comma >
Finish > Average the results. Does that work for you?

 

[Debbie D.]

Many thanks for your respone and assistance. However, the data is already in
seperate row may the following layout will explain better how the data is in
column

A B C D E F H
Grading
1 2 3 4 5 6 7
0 2 1 0 2 1 10

A - H are the Excel column labels
1 - 7 are the possible marks the survey has
0 - 10 are the number of times a person has received that mark for example:
In Column D the grade is 4 and no person (0) marked a grade of 4. However
in column H the grade is 7 and 10 responses from the survey that this person
a grade of 7. Ten people gave their manager a mark of 7.

Hope this helps and thanks again.

Debroah D. (UK)

 

[ryguy7272]

Ah! Ok, so put 1 2 3 4 5 6 7 in Columna A-G.
You can even do =column() in each column and it will count for you!

Then put your data in row 2, as such:0 2 1 0 2 1 10

Then, use a function like this:
=SUMPRODUCT((A2:G2)*(A1:G1))/SUM(A2:G2)

If you have lots of rows, try this:
=SUMPRODUCT((A2:G2)*($A$1:$G$1))/SUM(A2:G2)

 

[Debbie D.]

Ryan,

Excellent and perfection, thank you very much 100%

Debbie D. (UK)