averages

[pigsy101]

Hi All

Trying to work out the best way to get an average answer based on a
set of answers

The answer will be 1 - 10 across a number of questions

e.g
B C D E F
Answer Q1 Q2 Q3 Q4
1 1 1 3 3
2 1 0 1 2
3 0 1 3 3
4 1 0 1 1
5 1 1 6 7
6 2 2 5 2
7 2 3 3 4
8 2 1 7 6
9 1 2 1 3
10 2 2 8 7
Total 13 13 38 38
Average ? ? ? ?

What is the best way to get the average response to each question.

i've tried =SUMPRODUCT($B$2:$B$11,C2:C11)/SUM(C2:C11) but not sure
this is right.

Many thanks in advance.

 

[Bob Phillips]

Why not just =AVERAGE(C2:C11)?

 

[pigsy101]

Why not just =AVERAGE(C2:C11)?

--
__________________________________
HTH

Bob












- Show quoted text -

Because that will only give me the average of the answers which is
1.3. I need the average answer eg 1-10 based on the answers and the
number of people giving that answer.
I'll try and make it clearer
its a customer satisfaction survey so for example

On a rating of 1 - 10 how good was the service??

Rating - Number of people who gave that rating
1 - 2
2 - 3
3 - 0
4 - 2
5 - 5
6 - 8
7 - 4
8 - 5
9 - 11
10 - 7

So based on the number of responses in each answer category, what is
the average answer to the question??

I hope this makes it clearer

 

[Bernard Liengme]

With "marks" 1 to 10 in A1:A10 and number of replies in B1:B10
=SUMPRODUCT(A1:A10,B1:B10)/SUM(B1:B10)

Think of it like this. Use for the example
1 - 2
2 - 3
3 - 0
4 - 2

So for answers you have:
1 1 2 2 2 4 4 the average is (1+1+2+2+2+4+4)/7
OR (1*2 + 2*3 + 3*0 + 4*2)/7

Now do you see the SUMPRODUCT/SUM?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

Why not just =AVERAGE(C2:C11)?

--
__________________________________
HTH

Bob












- Show quoted text -

Because that will only give me the average of the answers which is
1.3. I need the average answer eg 1-10 based on the answers and the
number of people giving that answer.
I'll try and make it clearer
its a customer satisfaction survey so for example

On a rating of 1 - 10 how good was the service??

Rating - Number of people who gave that rating
1 - 2
2 - 3
3 - 0
4 - 2
5 - 5
6 - 8
7 - 4
8 - 5
9 - 11
10 - 7

So based on the number of responses in each answer category, what is
the average answer to the question??

I hope this makes it clearer

 

[joeu2004]

i've tried =SUMPRODUCT($B$2:$B$11,C2:C11)/SUM(C2:C11)
but not sure this is right.

Seems correct to me, based on the my interpretation of the
organization of the data. As you drag or copy the formula across the
row, C2:C11 will change to D211, but $B$2:$B$11 will correctly
remain the same.

The answer will be 1 - 10 across a number of questions

If you are saying that you want an integer result, you simply need to
round the result appropriately. For example:

=ROUND(SUMPRODUCT($B$2:$B$11,C2:C11)/SUM(C2:C11), 0)

Alternatively, I wonder if you are looking for a different kind of
average -- perhaps the median, not the mean.