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 cant use an
average formula.

Basically, what Im 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 cant 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