# Thread: Average Formula (XL 2000)

1. ## Average Formula (XL 2000)

Hello, I am trying to figure out how I can do this (hoping I can explained it right). I enclosed a spreadsheet if you'd like to take a look. What I would like to do is to find out what the average is of a survey sheet. I have ratings 0 to 5, 5 being excellent 1 being poor.

Once I have collected and entered the data from many different surveys on one sheet I come up with 5 people marking 1, 0 people 2, 2 people 3 and so on getting a total of 22 people rating the first "question" "How were the brochures/Handouts". Now I want to find out what is the average rating of that row? I created a formula =average(c12:H12) giving me a total of 3.67 if I have a zero in the empty cells, however if I delete the zeros I get 5.50 as a total????????? That doesn't sound right or is it??? Can someone help me on that? Thank you very much. Tira

2. ## Re: Average Formula (XL 2000)

What you want is a weighted average. The formula in J12 should be

=SUMPRODUCT(\$D\$11:\$H\$11,D12:H12)/SUM(D12:H12)

You can fill this down to J15. Explanation: in the first place, you shouldn't include column C (rating 0 = don't know/doesn't apply) in the average. If 5 people give a rating of 1, 0 a rating of 2, 2 a rating of 3, 7 a rating of 4 and 8 a rating of 5, the total rating is 5*1 + 0*2 + 2*3 + 7*4 + 8*5. The number of people is 5 + 0 + 2 + 7 + 8. So the average rating is (5*1 + 0*2 + 2*3 + 7*4 + 8*5)/(5 + 0 + 2 + 7 + 8). This is precisely what the formula does.

3. ## Re: Average Formula (XL 2000)

Wow, I guess I was a "little" off. I will try that on my actual sheet and hope it works. Thanks so much. Tira

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•