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

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?

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Debbie D." wrote:

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

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)
----

"Debbie D." wrote:

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

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)


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Debbie D." wrote:

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)
----

"Debbie D." wrote:

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

Ryan,

Excellent and perfection, thank you very much 100% :-)

Debbie D. (UK)

"ryguy7272" wrote:

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)


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Debbie D." wrote:

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)
----

"Debbie D." wrote:

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